Join Tables Help

[TapeGun007]

I have 3 tables:

 

States:

id

name

capital

 

Counties:

id

state_id

name

population

 

Recruiters:

id

state_id

county_id

Name

 

I have a drop down where you select the State, so the state name will be listed at the top.

 

I want the information in the table to have the following:

 

County - Population - Recruiter Name(s)

 

I'm guessing that the SQL will go something like this, but I'm not super great at mySQL.

            $sql = "SELECT *
                    FROM States s
                    LEFT JOIN Counties c
                        ON (id = state_id)
                    LEFT JOIN Recruiter r
                        ON (r.state_id = s.id AND r.county_id = c.id)
                    WHERE state_id='$StateID'
                    ";

Any help would be appreciated.

[benanamen]

Close. This should do it. Not so sure about the AND without testing. Thinking a recruiter is tied to a county which is tied to a state so the AND would not be needed. Are there Recruiters that are only tied to a state and not a county?

SELECT * 
FROM   states s 
       LEFT JOIN counties c 
              ON ( c.id = s.state_id ) 
       LEFT JOIN recruiter r 
              ON ( r.state_id = s.id 
                   AND r.county_id = c.id ) 
WHERE  state_id = '$StateID' 

Edited January 12, 2016 by benanamen

[TapeGun007]

Didn't work, but I just realized this may be due to another issue:

 

Some tables have the same names so using this:

echo $row['name']."<br />";

May not work correctly....

 

How can you differentiate?  I used the AND on another page and it works great.

 

So, yes, recruiters are in a specific state and county only.  I need the database to list the recruiters in each county and don't care about state level or nationwide.

[benanamen]

It would be easier if you could post a dump of your DB. As far as the names, just make them unique. states.state_name, recruiter.recruiter_name, counties.county_name

 

Also, you are mixing case around. Always use lower case for your column names, table names and variable names. It will save you trouble in the long run. Always be consistent in your naming

 

 

You said

 I need the database to list the recruiters in each county and don't care about state level 

 

But your WHERE condition is based on a state id.

Edited January 12, 2016 by benanamen

[TapeGun007]

Yeah, the issue with the database is that I didn't create it....

 

I'll have to go through and fix it, then try again with this code.

[Muddy_Funster]

if you stop using SELECT * you will be able to choose the output names for the columns.

 

nonsense example:

 

SELECT
  s.col1 AS state_name,
  s.col2 AS sColumn2,
  c.col1 AS county_name
FROM
  state s
INNER JOIN
 county c
...

even though the DB has a column called col1 in both tables once the query is processed then you will have the aliased names to work with in the PHP array

...
echo "State ---> {$row['state_name']} ";
echo "County ->{$row['county_name']}";
...

[Barand]

As muddy_funster said, don't use SELECT *.

 

You table output only requires county, population and recruiter names so those are all you need to select.

SELECT c.name as county_name
, c.population
, r.name as recruiter
FROM recruiters r
INNER JOIN counties c ON r.county_id = c.id
INNER JOIN states s ON c.state_id = states_id
WHERE s.id = $state_id
ORDER BY county_name

Alternatively, if you want all the recruiter names for a county in a single row you can

SELECT c.name as county_name
, c.population
, GROUP_CONCAT(r.name SEPARATOR ', ') as recruiters
FROM recruiters r
INNER JOIN counties c ON r.county_id = c.id
INNER JOIN states s ON c.state_id = states_id
WHERE s.id = $state_id
GROUP BY county_name

[TapeGun007]

Now that's the answer I was looking for... but didn't know how to ask.  I do not use SELECT * except when just initially writing a query and then trim it down once I know exactly what fields I want to include.  Bad habit from the looks of it.

 

I didn't know you could temporarily rename them like that.... perfect!  Thank you also Barand as you have helped me out of a jam many times.

[TapeGun007]

I had some time off and other work that was a higher priority.  

 

The code that Barand gave me works, I had to slightly modify it to:

SELECT c.name AS county_name, c.population, r.name AS recruiter
                    FROM recruiters r
                    INNER JOIN counties c ON r.county_id = c.id
                    INNER JOIN states s ON c.state_id = s.id
                    WHERE s.id = $StateID
                    ORDER BY county_name

I think I just had to change the state_id to s.id and it worked fine.  At some point I should go back and rename the table fields so it makes more sense.

 

In any case, I need every county to show regardless of whether or not it has a recruiter office there or not.  Then list all the recruiters in one field as suggested above.  Unfortunately the line "GROUP_CONCAT(r.name SEPARATOR ', ') as recruiters" caused nothing to return.

 

Thanks!

[Barand]

My data

mysql> select * from states;
+----+-------------+------------+
| id | name        | capital    |
+----+-------------+------------+
|  1 | Connecticut | Hartford   |
|  2 | Maine       | Augusta    |
|  3 | Vermont     | Montpelier |
+----+-------------+------------+

mysql> select * from counties;
+----+----------+------------+------------+
| id | state_id | name       | population |
+----+----------+------------+------------+
|  1 |        3 | Bennington |      37125 |
|  2 |        3 | Addison    |      36821 |
|  3 |        3 | Caledonia  |      31227 |
|  4 |        1 | Fairfield  |     916829 |
|  5 |        1 | Hartford   |     894014 |
|  6 |        2 | Aroostook  |      71870 |
|  7 |        2 | Cumberland |     281674 |
+----+----------+------------+------------+

mysql> select * from recruiters;
+----+-----------+---------+
| id | county_id | name    |
+----+-----------+---------+
|  1 |         1 | Anne    |
|  2 |         1 | Bernard |
|  3 |         2 | Charlie |
|  4 |         2 | Dianne  |
|  5 |         3 | Emma    |
|  6 |         4 | Fred    |
|  7 |         4 | George  |
|  8 |         6 | Henry   |
|  9 |         6 | Ian     |
| 10 |         6 | Jane    |
+----+-----------+---------+

query

SELECT c.name
, c.population
, group_concat(r.name separator ', ') as recruiters
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 = 2
GROUP BY c.name

results

+------------+------------+------------------+
| name       | population | recruiters       |
+------------+------------+------------------+
| Aroostook  |      71870 | Henry, Ian, Jane |
| Cumberland |     281674 | NULL             |
+------------+------------+------------------+

[TapeGun007]

Barand,

 

Thank you again.  I figured I would have to select from Counties instead of Recruiters and likely something would not be an Inner Join (in my limited knowledge).  I had just started to read up on the different joins.

 

I only had to modify the code above slightly so it worked with my PHP code already in place with

                    SELECT c.name AS county_name
                    , c.population
                    , group_concat(r.name separator ', ') 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

But it works perfectly!  Thank you so much!

 

-Tape