Jump to content

Join Tables Part 2


TapeGun007
Go to solution Solved by Barand,

Recommended Posts

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.

 

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 by TapeGun007
Link to comment
Share on other sites

 

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


Link to comment
Share on other sites

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 by TapeGun007
Link to comment
Share on other sites

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 by Barand
add results
Link to comment
Share on other sites

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 by TapeGun007
Link to comment
Share on other sites

  • Solution

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.