TapeGun007 Posted January 27, 2016 Share Posted January 27, 2016 Please read this first: http://forums.phpfreaks.com/topic/300283-join-tables-help/ I now want to add one more table with information: universities -- univ_id county_id name students state_id I read up fully on the differences in Inner Join vs Left Join so I understand what Barand did last time with the following: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id WHERE c.state_id = $StateID GROUP BY c.name So I attempted first to try my own hand at this: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter , group_concat(u.name separator '<br /> ') AS u_name FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON r.county_id = u.county_id WHERE c.state_id = $StateID GROUP BY c.name It works, but the university name appears twice. I ran the SQL directly into the database to see why and I got the following: Santa Clara // County Name 229 // County ID 1,894,605 // County Population Adecco<br /> Manpower // recruiter names Stanford<br /> Stanford // College name shows up twice, it should only be once So I added another recruiter as "test" and now "Stanford" appears 3 times. So for every recruiter there is, the university name duplicates itself. Theoretically, I presume the "recruiters" table would have to be a full outer join and then left join universities. I just read there is no full outer join and that you need to use a Union? Is this correct? I'm trying to learn, but just want to make sure I'm on the right track. I'm still testing various tries, but I'm not confident in my syntax. Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 27, 2016 Author Share Posted January 27, 2016 I took a stab at this, but the syntax is incorrect: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter , group_concat(u.name separator '<br /> ') AS uname FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id UNION RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON r.county_id = u.county_id WHERE c.state_id = 5 GROUP BY c.name Quote Link to comment Share on other sites More sharing options...
Barand Posted January 27, 2016 Share Posted January 27, 2016 what if you LEFT JOIN universities u ON c.id = u.county_id Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 28, 2016 Author Share Posted January 28, 2016 (edited) Sorry, I should've pointed to what line was getting the syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN ' at line 10 I thought maybe it's because I have "recruiters r" twice? So I removed "recruiters", same error. Edited January 28, 2016 by TapeGun007 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 28, 2016 Share Posted January 28, 2016 what if you LEFT JOIN universities u ON c.id = u.county_id I meant to use that in your earlier query SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter , group_concat(u.name separator '<br /> ') AS u_name FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON c.id = u.county_id WHERE c.state_id = $StateID GROUP BY c.name Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 28, 2016 Author Share Posted January 28, 2016 (edited) Right, I wasn't sure so I tested that as well. It works better than anything else I've tried because it now lists Universities where no recruiter exists (before it didn't), but it still repeats the name of the college for every recruiter in that county. Output: county_name | county_id | population | recruiter | u_name San Joaquin 225 715,597 Manpower<br /> Adecco NULL San Luis Obispo 226 279,083 NULL NULL San Mateo 227 758,581 Adecco NULL Santa Barbara 228 440,668 NULL Test College Santa Clara 229 1,894,605 Adecco<br /> test<br /> Manpower Stanford<br /> Stanford<br /> Stanford Santa Cruz 230 271,804 NULL NULL Shasta 231 179,804 Adecco NULL Sierra 232 3,003 NULL NULL Siskiyou 233 43,628 NULL NULL Solano 234 431,131 NULL NULL Sonoma 235 500,292 Manpower NULL Stanislaus 236 531,997 Adecco NULL Edited January 28, 2016 by TapeGun007 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 28, 2016 Share Posted January 28, 2016 (edited) try SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(DISTINCT r.name separator '<br /> ') AS recruiter , group_concat(DISTINCT u.name separator '<br /> ') AS uname FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id UNION RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON r.county_id = u.county_id WHERE c.state_id = 5 GROUP BY c.name results +--------------------+-----------+-----------+ | region | region_id | parent_id | +--------------------+-----------+-----------+ | Caribbean | 1 | 0 | | Eastern Caribbean | 1 | 1 | | Southern Caribbean | 1 | 1 | | Western Caribbean | 1 | 1 | | South America | 4 | 0 | | Amazon River | 4 | 4 | +--------------------+-----------+-----------+ Edited January 28, 2016 by Barand add results Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 28, 2016 Author Share Posted January 28, 2016 (edited) This is mySQL 5.5.42 (If that helps) and I get the following error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN ' at line 10 Edited January 28, 2016 by TapeGun007 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 29, 2016 Solution Share Posted January 29, 2016 Sorry, I copied and edited the wrong query SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(DISTINCT r.name separator '<br /> ') AS recruiter , group_concat(DISTINCT u.name separator '<br /> ') AS u_name FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON c.id = u.county_id WHERE c.state_id = $StateID GROUP BY c.name Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 29, 2016 Author Share Posted January 29, 2016 (edited) Yes, that works perfectly. I will go and study exactly what you did there. Thank you very much. Do you recommend any books to better learn mySQL? Edited January 29, 2016 by TapeGun007 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 29, 2016 Share Posted January 29, 2016 Sorry, I am unable to do that, I have never read one. Just reference manuals and experimentation. Quote Link to comment Share on other sites More sharing options...
thara Posted February 2, 2016 Share Posted February 2, 2016 This website will help you. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.