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')
精彩评论