CODEWITHSUNDEEP
sqlwhere-clause
daihovey
daihovey

Reputation: 3575

Multiple Where conditions

Having trouble getting this syntax right:

SELECT DISTINCT id 
FROM metadata 
WHERE (meta_key = 'school' AND meta_value = 'Some School') 
AND WHERE (meta_key = 'hidden' AND meta_value = '1')

Its failing at line 4...

UPDATED: Table looks like this:

meta_id - id - meta_key     - meta_value
1         1    school         Some School 1
2         1    hidden         0
3         2    school         Some School 2
4         2    hidden         1
5         3    school         Some School 3
6         3    hidden         0
7         4    school         Some School 4
8         4    hidden         0
9         5    school         Some School 5
10        5    hidden         1

UPDATED: I have a related, extended here Does row exist and multiple where

Upvotes: 3

Views: 720

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You do not need a second where, and the second and was probably intended to be an or:

SELECT DISTINCT id 
FROM metadata 
WHERE (meta_key = 'school' AND meta_value = 'Some School') 
OR (meta_key = 'hidden' AND meta_value = '1')

(the reason I think you wanted an or is because otherwise you have a conjunction of contradictory clauses meta_key = 'school' AND meta_key = 'hidden', which is always false).

EDIT : In response to OP's comment about the results he is trying to get, here is a different query:

SELECT DISTINCT m1.id 
FROM metadata m1
join metadata m2 on m1.id = m2.id
WHERE (m1.meta_key = 'school' AND m1.meta_value = 'Some School') 
AND (m2.meta_key = 'hidden' AND m2.meta_value = '1')

Now the conjunction clauses are no longer contradictory, because they refer to two different rows m1 and m2.

Upvotes: 5

E.Z. Hart
E.Z. Hart

Reputation: 5747

You can't have multiple WHERE clauses. I think what you mean is

SELECT DISTINCT id 
FROM metadata 
WHERE (meta_key = 'school' AND meta_value = 'Some School') 
    OR (meta_key = 'hidden' AND meta_value = '1')

Upvotes: 2

Related Questions