开发者

Strange SQL problem selecting multiple values for same column

开发者 https://www.devze.com 2022-12-21 10:14 出处:网络
Been at this for a few hours now and I can\'t make any sense of it. I\'ve used this way of selecting multiple values for same column a few times, but there is something weird with this one.

Been at this for a few hours now and I can't make any sense of it. I've used this way of selecting multiple values for same column a few times, but there is something weird with this one.

SELECT * FROM employee as s
INNER JOIN works AS w1 ON w1.name = s.name
INNER JOIN employee AS w2 ON w2.name = s.name
INNER JOIN employee AS w3 ON w3.name = s.name
WHERE w2.city = 'Washington'

Basically what I want to do is find all companies which have people in all the cities. The company name is under 'works'. The problem is however that if I have the WHERE w2.city='Washington' it will make ALL the cities match Washington when it should only touch w2 and leave w3 开发者_JAVA技巧alone so I could match it with another value.

Anyone know why its doing this? Or know a better way to do it.

Thank you very much in advance.


UPDATE:

Okay, I think I understand now what you're trying to do.

EXAMPLE:

SELECT s.* FROM Employee AS s
INNER JOIN Works AS w1 ON w1.Name = s.Name
INNER JOIN (
    SELECT e1.Name FROM Employee AS e1 WHERE e1.City = 'Washington'
    UNION
    SELECT e2.Name FROM Employee AS e2 WHERE e2.City = 'Atlanta'
) AS sub ON sub.Name = s.Name

ORIGINAL:

I'm assuming you're doing this repeated join to ultimately have multiple search criteria on the same column? Maybe you simplified the SQL for demonstration purposes but as it stands now i don't see the limitation that would require you to do the multiple join.

Perhaps simplifying your statement might work with the addition of a WHERE IN function.

EXAMPLE:

SELECT * FROM Employee AS s
INNER JOIN Works AS w1 ON w1.Name = s.Name
WHERE s.City IN ('Washington', 'Atlanta')
0

精彩评论

暂无评论...
验证码 换一张
取 消