Jump to content


Photo

Alternate solutions to using GROUP_CONCAT for mysql 3.23


  • Please log in to reply
3 replies to this topic

#1 dislodge112

dislodge112
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 22 October 2006 - 07:17 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 October 2006 - 08:51 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 dislodge112

dislodge112
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 25 October 2006 - 05:59 AM

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



#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 October 2006 - 05:20 PM

I don't understand... DISTINCT won't do anything here, table1.id is always unique; also, where are the counts?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users