SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to combine two queries - need help

    I've got two queries that I want to combine into one result set. My mysql query skills are not that great... Can someone tell me how to combine these two? I'm looking for the site_id's that are common between the two queries. i.e. s.site_id=distinct_s.site_id

    Thanks,
    Ken

    Code:
    SELECT s.site_id
    		FROM (
    			SELECT DISTINCT option_id
    			FROM submission_options
    			WHERE option_type = 'sitetype'
    			AND content_id=$content_id
    		) AS o
    		JOIN submission_sites AS s 
    		ON s.site_type_id = o.option_id
    and
    Code:
    SELECT distinct_s.site_id
      	FROM (
           SELECT DISTINCT site_id
             FROM submissions
           ) as distinct_s
    	LEFT OUTER
      	JOIN submissions as s
        	ON s.site_id = distinct_s.site_id
       		AND s.content_id = $content_id
     	WHERE s.site_id is null

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    common? as in, must be returned by both queries? or can be returned by either?

    if the former, use a join, if the latter, use a union
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm looking for the results to be common in both queries. I tried to do it with a join, but I'm new to joining multiple tables and seem to have a problem with my syntax. Heres the latest attempt that has problems. I'm hoping to learn from this so I can do it myself next time...

    Code:
    SELECT DISTICT site_type.site_id
    		FROM (
    			SELECT site_type.site_id
    			FROM (
    				SELECT DISTINCT option_id
    				FROM submission_options
    				WHERE option_type = 'sitetype'
    				AND content_id=13
    			) AS o
    		JOIN submission_sites AS s 
    			ON s.site_type_id = o.option_id
    			) as site_type,
    		(
    		SELECT distinct_s.site_id
      			FROM (
          	 	SELECT DISTINCT site_id
             	FROM submissions
           		) as distinct_s
    			LEFT OUTER
      			JOIN submissions as s
        		ON s.site_id = distinct_s.site_id
       			AND s.content_id = 13
     			WHERE s.site_id is null
    		) as unsubmitted
    		JOIN site_type
    		ON site_type.site_id=unsubmitted.site_id

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select site_id 
      from (
    SELECT s.site_id
      FROM (
       SELECT DISTINCT option_id
         FROM submission_options
        WHERE option_type = 'sitetype'
          AND content_id=$content_id
           ) AS o
    INNER
      JOIN submission_sites AS s 
        ON s.site_type_id = o.option_id
           ) as first_query
    inner 
      join (  
    SELECT s.site_id
      FROM (
       SELECT DISTINCT site_id
         FROM submissions
            ) as distinct_s
       LEFT OUTER
         JOIN submissions as s
           ON s.site_id = distinct_s.site_id
          AND s.content_id = $content_id
        WHERE s.site_id is null   
           ) as second_query  
      on first_query.site_id       
       = second_query.site_id
    rudy.ca | @rudydotca
    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
  •