AND in a WHERE clause on the same field?

Question

I have a table plasmid_table with key plasmid_id (not shown), which is associated to an antibiotic table (also not shown) with primary key antibiotic_id by way of the table below named plasmid_to_antibiotic_table.

plasmid_fk antibiotic_fk
14          1
15          1
16          1
17          1
18          1
19          1
20          1
26          4
26          5

I want to find the sole plasmid record that references both antibiotics 4 and 5, which is plasmid_fk 26, so I did a search as follows

SELECT plasmidName_col FROM plasmid_table 
INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 
WHERE (antibiotic_table.antibiotic_id = 4 AND antibiotic_table.antibiotic_id = 5)

but this yields no results. Does this mean that it's not possible to construct an AND search this way, and I would have to do two independent searches and union them?

Do OR instead of AND. A record can't have a column with two different values at the same time. The column is either 4 OR 5 not 4 AND 5. — Jason White, Jul 17 '20 at 21:35
`antibiotic_table.antibiotic_id` can't be 4 and 5 at the same time. Please review your logic. It's either 4 or 5, can't be both. Your statement `WHERE (antibiotic_table.antibiotic_id = 4 AND antibiotic_table.antibiotic_id = 5` will always return false. — Eric, Jul 17 '20 at 21:38

score 1 · Answer 1 · answered Jul 17 '20 at 21:44

You can check for both the ids in Where clause, and then Group BY the record, where the distinct count for the id should be 2.

SELECT pt.plasmidName_col FROM plasmid_table  pt
INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 
WHERE antibiotic_table.antibiotic_id IN (4,5)
Group BY pt.plasmidName_col
having count(distinct antibiotic_table.antibiotic_id) = 2

score -1 · Answer 2 · answered Jul 17 '20 at 21:41

Your WHERE always returns false, thus always no rows.

SELECT plasmidName_col FROM plasmid_table 
INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 
WHERE (antibiotic_table.antibiotic_id = 4 OR antibiotic_table.antibiotic_id = 5)

AND in a WHERE clause on the same field?

2 Answers2