sub select help

RGM · #1 (**permalink**) 07-26-11, 02:54

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

r937 · #2 (**permalink**) 07-26-11, 03:31

what happens when you remove btb.type_id = 2 from the WHERE clause?

RGM · #3 (**permalink**) 07-26-11, 04:00

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.

r937 · #4 (**permalink**) 07-26-11, 05:25

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

RGM · #5 (**permalink**) 07-26-11, 06:26

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?

r937 · #6 (**permalink**) 07-26-11, 12:27

Quote:

Originally Posted by RGM

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

dav1mo · #7 (**permalink**) 07-26-11, 16:05

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

RGM · #8 (**permalink**) 07-26-11, 16:22

^ 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?

r937 · #9 (**permalink**) 07-28-11, 08:46

Quote:

Originally Posted by RGM

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

RGM · #10 (**permalink**) 08-01-11, 22:07

Sorry fro the late reply, will check it out when I get in.

Thanks a lot for the help, appreciate it.

r937 · #11 (**permalink**) 08-01-11, 22:21

whoa, you've been out since thursday?

that's what i call a helluva date

RGM · #12 (**permalink**) 08-01-11, 22:45

*in that project

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

RGM · #13 (**permalink**) 08-03-11, 06:14

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