mysql WHERE XXX = ZZZ AND XXX = YYY

I am new to mySql and am trying to do a filtering query from one column using data from a few other tables. For example, I can pull a table of names, a table of their marks in the sports classes, and a table of sports.

I can query this database using

WHERE beta.sport = 'basketball'; pulls all of the beta id's for basketball and effectively all the kids who play basketball. Now I want to find all the kids who played basketball AND Lacrosee.

WHERE beta.sport = 'basketball' AND beta.sport = 'lacrosse';

The problem is that the query executes fine, but returns zero results. I know by manually looking at my data, that there are kids who play basketball and lacrosse. How do you filter the kids who play both basketball and lacrosse? OR returns kids that play either lacrosse or basketball. I only want to return kids that p开发者_JS百科lay both and the only access I have to the list of sports is from this one table beta.

TABLE beta

beta_id - sport ||
1  - basketball ||
2  - lacrosse ||
3  - football ||
4  - basketball ||

---

SELECT  *
FROM    names n
WHERE   (
        SELECT  COUNT(*)
        FROM    beta b
        WHERE   b.sport IN ('basketball', 'lacrosse')
                AND b.name = n.id
        ) = 2

---

You need to change the AND with an OR

AND does not means:

I want all kids who play basketball AND all kids who play lacrosse.

What it means is I want all all kids who play both lacrosse and basketball.

When using OR get used to wrapping your condition in brackets as such

WHERE (beta.sport = 'basketball' OR beta.sport = 'lacrosse')

---

WHERE BETA.SPORT = 'BASKETBALL' OR BETA.SPORT = 'LACROSSE'

Note de OR, instead of AND.

---

"WHERE beta.sport = 'basketball' AND beta.sport = 'lacrosse';" this can't return any rows since you're asking for beta.sport to be BOTH basketball and lacrosse... Try OR... WHERE beta.sport = 'basketball' OR beta.sport = 'lacrosse';

You might try a subselect of both...

select * from sportsTable 
where itemID in 
(select itemID from sportsTable WHERE beta.sport = 'basketball') 
and itemID in 
(select itemID from sportsTable WHERE beta.sport = 'lacrosse')