TapeGun007 Posted January 12, 2016 Share Posted January 12, 2016 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. Quote Link to comment Share on other sites More sharing options...
benanamen Posted January 12, 2016 Share Posted January 12, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 12, 2016 Author Share Posted January 12, 2016 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. Quote Link to comment Share on other sites More sharing options...
benanamen Posted January 12, 2016 Share Posted January 12, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 12, 2016 Author Share Posted January 12, 2016 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. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted January 12, 2016 Share Posted January 12, 2016 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']}"; ... Quote Link to comment Share on other sites More sharing options...
Barand Posted January 12, 2016 Share Posted January 12, 2016 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 Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 12, 2016 Author Share Posted January 12, 2016 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. Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 25, 2016 Author Share Posted January 25, 2016 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! Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 25, 2016 Solution Share Posted January 25, 2016 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 | +------------+------------+------------------+ Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted January 25, 2016 Author Share Posted January 25, 2016 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 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.