Unique_ID List_ID Locality Common_name A1 001 Park Downy Woodpecker A2 001 Park Hairy Woodpecker A3 001 Park Carolina Chickadee B1 002 Campground Blue Jay B2 002 Campground Hairy Woodpecker C1 003 Backyard Downy Woodpecker C2 003 Backyard American Goldfinch D1 004 School American Goldfinch D2 004 School Hairy Woodpecker E1 005 Park Downy Woodpecker E2 005 Park Carolina Chickadee
SELECT List_ID, LOCALITY, COMMON_NAME FROM table WHERE COMMON_NAME = 'Downy Woodpecker' and COMMON_NAME = 'Hairy Woodpecker';
But returned no results. My table has 1,000s of observations, it's based on eBird data and those two species are generally common throughout the country, so there has to be at least one list_ID where they occur at the same time. In my example table, only Park (based on list_ID 001) meets the condition for what I'm looking for.
If I understand
IN, it will return a row that meets either. Any of the example list_IDs would work for that query, but that's not what I want. How do I write a query that forces the
WHERE to meet multiple conditions?