Python Forum

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

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?

What SQL are you using? The dialects have incompatibilities.

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

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

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;

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!