What is wrong with this multi-criteria WHERE statement?

by

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)

More Related Contents:

  1. Max sal to print base on manager_id
  2. Foreign key constraint may cause cycles or multiple cascade paths?
  3. How do I perform an IF…THEN in an SQL SELECT?
  4. Inserting multiple rows in a single SQL query? [duplicate]
  5. Passing a varchar full of comma delimited values to a SQL Server IN function
  6. In SQL, how can you “group by” in ranges?
  7. How to combine date from one field with time from another field – MS SQL Server
  8. How to do a case sensitive search in WHERE clause (I’m using SQL Server)?
  9. SQL Server – stop or break execution of a SQL script
  10. Pivot Table and Concatenate Columns
  11. Natural join in SQL Server
  12. Pivot using SQL Server 2000
  13. Transfer data from one database to another database
  14. Sequence vs identity
  15. How to update and order by using ms sql
  16. Using Excel VBA to run SQL query
  17. How to store image in SQL Server database tables column [duplicate]
  18. Reference an alias elsewhere in the SELECT list
  19. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  20. The backend version is not supported to design database diagrams or tables
  21. Split string and take last element
  22. Can I set ignore_dup_key on for a primary key?
  23. Selecting first row per group
  24. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  25. How do I add a auto_increment primary key in SQL Server database?
  26. SQL Row_Number() function in Where Clause
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment