dislodge112

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

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.