help with SQL query

[Larz60+]

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

[ichabod801]

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?

[micseydel]

What SQL are you using? The dialects have incompatibilities.

[Larz60+]

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

[Larz60+]

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

[buran]

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;

[Larz60+]

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!