Complex SQL Subquery


SELECT
	campaigns.id AS campaign_id, users_sponsors.id AS sponsor_id,
	IF(campaigns.promotedUntil IS NULL, 0, IF(campaigns.promotedUntil > CURDATE(), 1, 0)) AS promotedRanking,
	IF(campaigns.promotedUntil IS NULL, campaigns.max_cost_per_lead, 0) AS priceRank
FROM `campaigns`
RIGHT OUTER JOIN users_sponsors ON campaigns.uid = users_sponsors.id
WHERE
	users_sponsors.business_category = 8 AND
	campaigns.paid = 1 AND
	EXISTS
	(
		SELECT credit_cards.id
		FROM `credit_cards`, `users_sponsors`
		WHERE
			credit_cards.suid = users_sponsors.id AND
			users_sponsors.id = sponsor_id AND
			`authorized` != '0000-00-00 00:00:00'
		LIMIT 1
	)
ORDER BY
	promotedRanking DESC,
	priceRank DESC,
	campaigns.date_added DESC
LIMIT 5

(Ignore the “magic number” 8 for the business_category…that’s just a test)

The fact that I’ve been able to get myself to create a query this complex is really surprising to me. I’m trying to gather sponsor campaigns from the database with certain conditions. First of all, if they’ve been manually “promoted,” they need to appear at the top of the list. I also need to check if the campaign’s sponsor’s user account has a valid (authorized) credit card on file. As you can see in the subquery, I tried to use the “sponsor_id” from the main query, but that returns an error. I don’t know of another way to do what I want, and hopefully someone here understands what I’m trying to do and has more knowledge than I do of subqueries, unions, groups, joins, etc… because my experience is minimal.

I’ve also attached an sql file with each table’s structure.

Any help is appreciated! Thank you!

try this (untested) –

SELECT campaigns.id AS campaign_id
     , users_sponsors.id AS sponsor_id
     , CASE WHEN campaigns.promotedUntil > CURRENT_DATE
            THEN 1
            ELSE 0 END  AS promotedRanking
     , CASE WHEN campaigns.promotedUntil IS NULL
            THEN campaigns.max_cost_per_lead
            ELSE 0 END  AS priceRank
  FROM users_sponsors 
INNER
  JOIN campaigns
    ON campaigns.uid = users_sponsors.id
   AND campaigns.paid = 1 
 WHERE users_sponsors.business_category = 8 
   AND EXISTS
       ( SELECT id
           FROM credit_cards
          WHERE suid = users_sponsors.id 
            AND authorized <> '0000-00-00 00:00:00' )
ORDER 
    BY promotedRanking DESC
     , priceRank DESC
     , campaigns.date_added DESC
LIMIT 5