Jump to content

Alternate solutions to using GROUP_CONCAT for mysql 3.23


dislodge112

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.
Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.