Join Tables Part 2

[TapeGun007]

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.

 

[TapeGun007]

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

[Barand]

what if you

LEFT JOIN universities u ON c.id = u.county_id

[TapeGun007]

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

[Barand]

 

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

[TapeGun007]

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

[Barand]

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

[TapeGun007]

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

[Barand]

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

[TapeGun007]

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

[Barand]

Sorry, I am unable to do that, I have never read one. Just reference manuals and experimentation.

[thara]

This website will help you.