Stuck with mySQL query

[Kingy]

So I currently have two mysql tables. One called company and the other is for locations

 

Company: id, name

Location: id, companyid, city

 

Within the location table one company can have multiple cities. ie

 

id, companyid, location

1, 1, City1

2, 1, City2

3, 1, City3

4, 2, City2

5, 3, City1

6, 3, City3

 

Now what I'd like to do is when I run a search on 2 cities I would like to find the companies that have offices in both locations.

 

For example: I do a search for City1 and City2. The result would be companyid 1

Another example: I do a search for City1 and City3. The result would be companyid 1 and 3

 

At the moment I'm doing the following query (I'll do the join to company table once I figure this out):

 

SELECT companyid

FROM location

WHERE city = 'City1' and city = 'City2'

 

However, this is returning 0 results. Even thought I'm expecting companyID 1 to return. Am I doing something so obviously wrong and I'm not seeing it or is my data structure so messed up that it won't allow this sort of query?

 

Cheers

[requinix]

The city cannot be both "City1" and "City2" at the same time.

 

JOIN the table against itself.

SELECT l1.companyid
FROM location l1
JOIN location l2 ON l1.companyid = l2.companyid
WHERE l1.city = "City1" AND l2.city = "City2"

[Kingy]

That'll do the trick. Thanks very much