Jump to content

Join Tables Help


TapeGun007
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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']}";
...
Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

  • 2 weeks later...

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!

Link to comment
Share on other sites

  • Solution

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

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

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.