What is wrong with this multi-criteria WHERE statement?

The SQL below doesn't return the correct results. However, when I manually query the State table for 'STATEFP = 6' and insert the PK of that result into a query on the County table (WHERE stateid = 2004 AND countyfp = 45), I get the desired results.

SELECT * FROM Features
INNER JOIN County ON Featur开发者_开发百科es.COUNTY_NUMERIC = County.COUNTYFP
INNER JOIN State ON Features.STATE_NUMERIC = State.STATEFP
WHERE (County.COUNTYFP = 45) AND (State.STATEFP = 6)

The county returned appears to be the first county that matches. For example, every state has a county with countyfp = 100. It appears this sql is grabbing the first 100 it finds and with no regard for the state. The state must be taken into account.

What am I doing wrong here?

The features table has a column, CountyId. That column links us to the County table. The County table has a column StateId that links us back to the State table.

---

Perhaps the join between FEATURES and County orFEATURES and State is excluding the results you expect to see? Try changing it to a left join and see if you get the expected results:

SELECT * FROM Features
LEFT JOIN County ON Features.COUNTY_NUMERIC = County.COUNTYFP
LEFT JOIN State ON Features.STATE_NUMERIC = State.STATEFP
WHERE (County.COUNTYFP = 45) AND (State.STATEFP = 6)

UPDATE:

Based on your update - The County table has a column StateId that links us back to the State table.

Could the issue be that you should be joining County and State instead of County and FEATURES? Example:

SELECT * FROM Features
INNER JOIN County ON Features.COUNTY_NUMERIC = County.COUNTYFP
INNER JOIN State ON County.STATE_NUMERIC = State.STATEFP
WHERE (County.COUNTYFP = 45) AND (State.STATEFP = 6)

---

It sounds like the correct records exist in County and State, but not Features. Does this return any rows?

SELECT * FROM Features
WHERE (COUNTY_NUMERIC = 45) AND (STATE_NUMERIC = 6)

---

However, when I manually query the State table for 'STATEFP = 6' and insert the PK of that result into a query on the County table (WHERE stateid = 2004 AND countyfp = 45) . . . It appears this sql is grabbing the first 100 it finds and with no regard for the state. The state must be taken into account.

No table def, so this is a guess, but it looks like the query is not not narrowing the counties down by state the way you did manually. Maybe something like:

SELECT * FROM Features
INNER JOIN State ON Features.STATE_NUMERIC = State.STATEFP
INNER JOIN County 
    ON Features.COUNTY_NUMERIC = County.COUNTYFP
    AND State.an_appropriate_column = county.an_appropriate_state_identifier
WHERE (County.COUNTYFP = 45) AND (State.STATEFP = 6)

---

It seems to me like all the rows in Features do not have Features.COUNTY_NUMERIC and Features.STATE_NUMERIC populated properly.

Can you look at the actual data and verify?

If that is the case, the rows that have either value NULL will show up in multiple result sets.

In such cases, cleaning up the data is a much better option than using a LEFT JOIN

EDIT: Column Names

Are you joining on the wrong columns? Should you be using COUNTYID and STATEID instead of the corresponding FP columns?

SELECT * FROM Features
INNER JOIN County ON Features.COUNTY_NUMERIC = County.COUNTYID
INNER JOIN State ON Features.STATE_NUMERIC = State.STATEID
WHERE (County.COUNTYFP = 45) AND (State.STATEFP = 6)