Where statement problems

[themistral]

Hi guys,

 

I'm having a problem sorting out a WHERE clause.

 

Table contents:

eid

cid

1

1

1

2

1

3

2

1

2

2

2

3

3

3

 

SELECT eid FROM table WHERE cid = 1 AND WHERE cid = 1 AND cid = 3;

 

I would expect to get 1 and 2 returned as the value of eid but I am getting no results.

 

The strange thing is that if I change the AND to an OR, it returns results.

Why is it not working with an AND? 

 

Thanks guys!

[PFMaBiSmAd]

Your query has 2 WHERE clauses, so it produces a sql syntax error.

 

If your query is in fact -

 

SELECT eid FROM table WHERE cid = 1 AND cid = 3;

 

It won't match any rows because the WHERE clasue is always FALSE because cid cannot have more than one value in any row.

 

OR is correct because it matches the rows WHERE cid =1 OR cid =3. The WHERE clause is TRUE for the rows with 1 and 3.

[PFMaBiSmAd]

If your question is actually to get the eid's that have cid's with only both values (not just one of the values), you would need to use a HAVING COUNT() = 2 in it, along with the OR (which is the same as an IN()).

 

Untested but should work -

 

SELECT eid, count(*) as cnt FROM table WHERE cid IN(1,3) GROUP BY eid HAVING cnt =2;

 

You can also use a self join, but I don't recall how to do that off of the top of my head.

[themistral]

Thanks PFMaBiSmAd!

 

That has worked a treat!

I haven't used the HAVING clause before so I will have a look into that!

Thanks again!

 

Oh, and yes, having to WHERE's in my original statement was a mistake - I know not to have 2 of those