Select based on the number of appearances of an id in another table

Question

I have a table B with cids and cities. I also have a table C that has these cids with extra information. I want to list all the cids in table C that are associated with ALL appearances of a given city in Table B.

My current solution relies on counting the number of times the given city appears in Table B and selecting only the cids that appear that many times. I don't know all the SQL syntax yet, but is there a way to select for this kind of pattern?

My current solution:

SELECT Agents.aid
FROM Agents, Customers, Orders
WHERE (Customers.city='Duluth') 
AND (Agents.aid = Orders.aid) 
AND (Customers.cid = Orders.cid)
GROUP BY Agents.aid
HAVING count(Agents.aid) > 1

It only works because I know right now with the HAVING statement.

Thanks for the help. I wasn't sure how to google this problem, since it's pretty specific.

EDIT: I'm pinpointing my problem a bit. I need to know how to determine if EVERY row in a table has a certain value for a field. Declaring a variable and counting the rows in a sub-selection and filtering out my results by IDs that appear that many times works, but It's really ugly.

There HAS to be a way to do this without explicitly count()ing rows. I hope.

Answer 1

To start with, you can (and should) use JOIN instead of doing it in the WHERE clause, e.g.,

select Agents.aid
from Agents
join Orders on Agents.aid = Orders.aid
join Customers on Customers.cid = Orders.cid
where Customers.city = 'Duluth'
group by Agents.aid
having count(Agents.aid) > 1

After that, I'm afraid I might be a little lost. Using the table names in your example query, what (in English, not pseudocode) are you trying to retrieve? For example, I think your sample query is retrieving the PK for all Agents that have been involved in at least 2 Orders involving Customers in Duluth.

Also, some table definitions for Agents, Orders, and Customers might help (then again, they might be irrelevant).

Answer 2

Not an answer to your question, but a general improvement. I'd recommend using JOIN syntax to join your tables together.
This would change your query to be:

SELECT Agents.aid
FROM Agents
INNER JOIN Orders
ON Agents.aid = Orders.aid
INNER JOIN Customers
ON Customers.cid = Orders.cid
WHERE Customers.city='Duluth' 
GROUP BY Agents.aid
HAVING count(Agents.aid) > 1

What variant of SQL are you using?

Answer 3

I'm not sure if I understood you problem, but I think the following query is what you want:

    SELECT *
      FROM customers b
INNER JOIN orders c USING (cid)
     WHERE b.city = 'Duluth'
       AND NOT EXISTS (SELECT 1
                         FROM customers b2
                        WHERE b2.city = b.city
                          AND b2.cid <> cid);

Probably you will need some indexes on these columns.