Jump to content

Archived

This topic is now archived and is closed to further replies.

dislodge112

Alternate solutions to using GROUP_CONCAT for mysql 3.23

Recommended Posts

i have a table of peoples names and ids.  i have a second table of id references (to the person) and links.
the second table has a column which indicates what type of link it is...

i'm trying to write a query that will retrieve a row from the table of people, and include columns indicating whether a link of type 1, type 2, etc exist in the second table.  my initial attempts involve selecting on COUNT(table2.linktype='type1'), COUNT(table2.linktype='type2') with a LEFT JOIN between the two tables on table1.id = table2.idreference and GROUP BY table1.id.

this does not give the results i would like, because COUNT isn't counting just when the linktype is type1 or just when it's type2.

the best alternative i could find was using GROUP_CONCAT (and then just searching for whether or not type1, type2 exist in the GROUP_CONCAT column), but that doesn't exist in mysql 3.23.

i guess the basic structure of my question is, can you select a row from one table and add columns to it that indicate whether something exists in another table based on a specific condition.  i know that if it comes down to it i could just run a second query on table2 and group by linktype and get a sum, but i'm trying to avoid this.

thanks.

Share this post


Link to post
Share on other sites
Well, you could use a derived table to get all the counts grouped by type AND userid, and then left join this to the users table.

Share this post


Link to post
Share on other sites
ahh, figured it out... the trick was to left join on table2 twice and then get distinct id's from table1... like so:

SELECT DISTINCT table1.id, table1.name, table2a.linktype, table2b.linktype
FROM  table1
LEFT  JOIN table2 AS table2a ON table1.id = table2a.personid AND table2a.linktype =  "type1"
LEFT  JOIN table2 AS table2b ON table1.id = table2b.personid AND table2b.linktype =  "type2"
WHERE...

the derived table suggestion was good, but not as efficient as this... thank you though

Share this post


Link to post
Share on other sites
I don't understand... DISTINCT won't do anything here, table1.id is always unique; also, where are the counts?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.