Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: sub select help

  1. #1
    Join Date
    Feb 2011
    Posts
    29

    Unanswered: sub select help

    I got this far with the help of an old 2009 post of Rudy's.

    The only problem with the below is that the GROUP_CONCAT only has type 2 from the where. Bios could have other types which I also need.

    Is there a way I can get all the types associated with a bio? I figured a sub select but I haven't had any luck so far.

    Thank you for any help.



    Code:
    SELECT b.*
    			 , ba.display_name
    			 , ba.permalink as author_permalink
    			 , GROUP_CONCAT(bt.name) AS types
    			 , COALESCE(bc.comments,0) AS comments
    		FROM CL_bio b
    		
    		LEFT OUTER
    			  JOIN ( SELECT bio_id
    				    	  , COUNT(*) AS comments
    			  		 FROM CL_bio_comments
    			  	     GROUP BY bio_id ) AS bc
    				ON bc.bio_id = b.id
    
    		LEFT OUTER
    			  JOIN CL_blog_authors ba
    			    ON ba.blog_author_id = b.author_id
    			
    		LEFT OUTER
    			  JOIN CL_bio_types_to_bios btb
    			    ON btb.bio_id = b.id
    			
    		LEFT OUTER
    			  JOIN CL_bio_types bt
    			    ON bt.id = btb.type_id  
    			
    		WHERE btb.type_id = 2 AND b.published = "yes"
    		GROUP BY b.id
    		ORDER BY b.date_added DESC
    		LIMIT 0, 5

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happens when you remove btb.type_id = 2 from the WHERE clause?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    29
    It works as I want it to. I mean it does return the concatenated list of each type the bio has.
    That was it's primary purpose but now I've been asked to 'filter' by the types.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you can filter by the type, by adding conditions like btb.type_id = 2 to the WHERE clause?

    i guess i don't understand the question then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2011
    Posts
    29
    Yes it does filter the result set by the type which is what I what.

    What it does that I don't want it to is that it returns only the filtered type in the concatenated list.

    So say a bio has type 1,2 & 3.
    Another has 1 & 2
    A third has 4.

    I search for type two.

    I want to see results for the first two rows. In the concatenated part of the query results I'd want to see 1,2,3 and 1,2 instead both results have just 2 in the concatenated part.

    Did I make that clearer or worse?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    Did I make that clearer or worse?
    it's clear now

    you want to search for a specific bio type, but also see all the types for the bios which have that type

    i think

    before i attempt the query, could you please give me a brief verbal description of the purpose of each table, and identify which keys are PKs and which keys are FKs for all of the joins involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The quickest/easiest way I know is an exists clause. Also, I think your join to the BTB table should be an INNER join as you are turning it into an inner join with your WHERE clause. Something like:

    Code:
    SELECT b.*
    			 , ba.display_name
    			 , ba.permalink as author_permalink
    			 , GROUP_CONCAT(bt.name) AS types
    			 , COALESCE(bc.comments,0) AS comments
    		FROM CL_bio b
    		
    		LEFT OUTER
    			  JOIN ( SELECT bio_id
    				    	  , COUNT(*) AS comments
    			  		 FROM CL_bio_comments
    			  	     GROUP BY bio_id ) AS bc
    				ON bc.bio_id = b.id
    
    		LEFT OUTER
    			  JOIN CL_blog_authors ba
    			    ON ba.blog_author_id = b.author_id
    			
    		INNER 
    			  JOIN CL_bio_types_to_bios btb
    			    ON btb.bio_id = b.id
    			
    		LEFT OUTER
    			  JOIN CL_bio_types bt
    			    ON bt.id = btb.type_id  
    			
    		WHERE b.published = "yes"
                                  AND exists (select 1 from CL_bio_types_to_bios btb2
                                                  where btb2.type_id = 2
                                                     AND btb2.bio_id = btb.bio_id)
    		GROUP BY b.id
    		ORDER BY b.date_added DESC
    		LIMIT 0, 5
    Dave Nance

  8. #8
    Join Date
    Feb 2011
    Posts
    29
    ^ Thank you. I'll try that when I get back in.


    Rudy, sure thing. I've also attached the rendered display of what the query produces.

    The key along the top is the four types, they're also in the drop down on the left.

    The entry shown has one type (green icon) but could have all four. The query without the 'where btb' will get all types it has.

    When using the drop down I need to find the entries of that type, but also get all the types associated with it to output those (damn) little icons.

    Bio's table.
    PK = id
    Purpose: Stores 'blog post' like data. (Title, excerpt, date_added etc)

    Bio_types table.
    PK: id
    Purpose: Stores the label and icon for each type. Types are dynamic.

    Bio_types_to_bios table.
    PK: id
    FK: bio_id. Joins to bio.id
    FK: type_id. Joins to bio_types.id
    Purpose: Joins a bio to many types

    Bio_comments table.
    PK: id
    FK: bio_id. Joins to bio.id
    Purpose: Stores a user generated comment on a bio.


    Is that enough detail?
    Attached Thumbnails Attached Thumbnails grab.jpg  
    Last edited by RGM; 07-26-11 at 17:31.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    I search for type two.
    therefore this should be the "driving" condition in the query
    Code:
    SELECT b.*
         , ba.display_name
         , ba.permalink as author_permalink
         , btb2.types
         , COALESCE(bc.comments,0) AS comments
      FROM CL_bio_types_to_bios AS btb2
    INNER
      JOIN CL_bio AS b
        ON b.id = btb2.bio_id 
       AND b.published = 'yes'
    LEFT OUTER
      JOIN ( SELECT bio_id
                  , COUNT(*) AS comments
               FROM CL_bio_comments
             GROUP 
                 BY bio_id ) AS bc
        ON bc.bio_id = b.id
    LEFT OUTER
      JOIN CL_blog_authors ba
        ON ba.blog_author_id = b.author_id
    LEFT OUTER
      JOIN ( SELECT btb.bio_id
                  , GROUP_CONCAT(bt.name) AS types
               FROM CL_bio_types_to_bios AS btb
             INNER
               JOIN CL_bio_types AS bt
                 ON bt.id = btb.type_id  
             GROUP 
                 BY btb.bio_id ) AS btbs
        ON btbs.bio_id = b.id
     WHERE btb2.type = 2
    ORDER 
        BY b.date_added DESC
    note that the FROM clause starts with the btb2 table, and this, coupled with the WHERE clause, is what drives the query and decides which bios are to be returned

    then for every type 2 bio, it retrieves the btbs in a subquery, much like the comments were retrieved in a subquery, such that there is only one row per bio being joined onto each type 2 bio
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2011
    Posts
    29
    Sorry fro the late reply, will check it out when I get in.

    Thanks a lot for the help, appreciate it.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoa, you've been out since thursday?

    that's what i call a helluva date

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2011
    Posts
    29
    *in that project

    Hah a date! That would be something.. work and young ones don't compare.

  13. #13
    Join Date
    Feb 2011
    Posts
    29
    For completeness..

    Thanks a lot for the help on this one! I made a few little changes in there for a couple (I'm pretty sure deliberate ) mistakes. I added in the option to filter by title too.

    Final that seems to work great for all conditions:

    Code:
    		SELECT b.*
    		, ba.display_name
         		, ba.permalink as author_permalink
         		, btbs.types
         		, COALESCE(bc.comments,0) AS comments
    	  	
    		FROM CL_bio_types_to_bios AS btb2
    		INNER
    	  		JOIN CL_bio AS b
    			ON b.id = btb2.bio_id 
    	   		AND b.published = "yes"
    			AND b.title LIKE "'.$letter.'%"
    		LEFT OUTER
    	  		JOIN ( SELECT bio_id
    				  	, COUNT(*) AS comments
    			   	   FROM CL_bio_comments
    			 	   GROUP 
    				   BY bio_id ) AS bc
    			ON bc.bio_id = b.id
    		LEFT OUTER
    	  		JOIN CL_blog_authors ba
    			ON ba.blog_author_id = b.author_id
    		LEFT OUTER
    	  		JOIN ( SELECT btb.bio_id
    				  , GROUP_CONCAT(bt.name) AS types
    			   	  FROM CL_bio_types_to_bios AS btb
    			      INNER
    			          JOIN CL_bio_types AS bt
    				      ON bt.id = btb.type_id  
    			      GROUP 
    				  BY btb.bio_id ) AS btbs
    			ON btbs.bio_id = b.id
    	 	WHERE btb2.type_id = '.$type_id.'
    		GROUP BY b.id
    		ORDER 
    		BY b.date_added DESC
    		LIMIT '.$offset.', '.$limit

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why did you put a GROUP BY clause in the outer query???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2011
    Posts
    29
    Without it I was getting multiple results for the same bio entry.. it was the one bit I wasn't 100% on.

Posting Permissions

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