help with SQL query - Printable Version

help with SQL query - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Forum & Off Topic (https://python-forum.io/forum-23.html)
+--- Forum: Bar (https://python-forum.io/forum-27.html)
+--- Thread: help with SQL query (/thread-12574.html)

help with SQL query - Larz60+ - Aug-31-2018

It's been a while since I've done much of any SQL, I want to extract county code from county table and add it to results of places query where the CountyNames match. Here's my attempt which doesn't work:

SELECT (StateCd, CountyName, PlaceCd, StateAbbr, PlaceName, PlaceType, Fstat) FROM places (CountyCd, CountyName) from county where county.CountyName = places.CountyName order by StateCd, CountyCd, PlaceCd;

I think it's close to this:

SELECT StateCd, CountyName, PlaceCd, StateAbbr, PlaceName, PlaceType, Fstat  FROM places 
INNER JOIN  county ON places.StateCd = county.StateCd AND places.CountyName = county.CountyName
order by StateCd, CountyCd, PlaceCd;

but I get syntax error on = don't know which one

RE: help with SQL query - ichabod801 - Aug-31-2018

What about using?

SELECT StateCd, CountyName, PlaceCd, StateAbbr, PlaceName, PlaceType, Fstat  FROM places 
INNER JOIN  county USING (StateCd, CountyName)
order by StateCd, CountyCd, PlaceCd;

Or have you tried parentheses around the condition?

RE: help with SQL query - micseydel - Aug-31-2018

What SQL are you using? The dialects have incompatibilities.

RE: help with SQL query - Larz60+ - Aug-31-2018

Ichabod's join is almost there, there one column that's in both tables, so it barfs on StateAbbr,

Error:
Error: ambiguous column name: StateAbbr

Micseydel - This app uses sqlite3.

This is what the county table looks like:
CountyCd, StateCd, CountyName, StateAbbr, FipsClassCd

RE: help with SQL query - Larz60+ - Aug-31-2018

I also tried:

sqlite> SELECT StateCd, CountyName, PlaceCd, StateAbbr, PlaceName, PlaceType, Fstat  FROM places
   ...> LEFT JOIN county ON (places.StateCd = county.StateCd, places.CountyName = county.CountyName);
Error: ambiguous column name: StateCd

different column, same error

RE: help with SQL query - buran - Aug-31-2018

I think you need to specify from which table comes each column

SELECT p.StateCd, p.CountyName, c.CountyCd, p.PlaceCd, p.StateAbbr, p.PlaceName, p.PlaceType, p.Fstat
FROM places AS p
LEFT JOIN county AS c
ON p.StateCd = c.StateCd AND p.CountyName = c.CountyName;

RE: help with SQL query - Larz60+ - Aug-31-2018

Buran - You got it!

I used to write some god awful Oracle queries, but it's been years since I did anything other that simple queries.
A Join is simple, but it's like trying to get on the green in two when you haven't golfed in ten years!

Thanks!