SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot Beckwith's Avatar
    Join Date
    Mar 2004
    Location
    California
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex SQL Subquery

    Code SQL:
    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!
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this (untested) --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •