dislodge112 Posted October 22, 2006 Share Posted October 22, 2006 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 https://forums.phpfreaks.com/topic/24765-alternate-solutions-to-using-group_concat-for-mysql-323/ Share on other sites More sharing options...
fenway Posted October 22, 2006 Share Posted October 22, 2006 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. Link to comment https://forums.phpfreaks.com/topic/24765-alternate-solutions-to-using-group_concat-for-mysql-323/#findComment-112804 Share on other sites More sharing options...
dislodge112 Posted October 25, 2006 Author Share Posted October 25, 2006 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.linktypeFROM 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 https://forums.phpfreaks.com/topic/24765-alternate-solutions-to-using-group_concat-for-mysql-323/#findComment-114029 Share on other sites More sharing options...
fenway Posted October 26, 2006 Share Posted October 26, 2006 I don't understand... DISTINCT won't do anything here, table1.id is always unique; also, where are the counts? Link to comment https://forums.phpfreaks.com/topic/24765-alternate-solutions-to-using-group_concat-for-mysql-323/#findComment-114905 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.