MySQL查询同一城市的不同表

Question

表格:

Processor(_pid_, brand, clockSpeed, supplierCity)

Memory(_mid_, brand, capacity, supplierCity)

Harddrive(_hdid_, brand, capacity, RPM, supplierCity)

PC(_pcid_, _mid_, _pid_, _hdid_, model, price, cost)

我正在尝试构建一个查询:

Find's the PC model whose price is less than 1000 pounds and for which 
the memory and the processor are supplied from the same city

我当前的查询返回相同结果的多次重复:

SELECT pc.model FROM pc, memory, processor WHERE
memory.supplierCity=processor.supplierCity AND pc.price<1000;

Answer 1

SELECT  *
FROM    pc
WHERE   price < 1000
        AND EXISTS
        (
        SELECT  NULL
        FROM    processor p
        JOIN    memory m
        USING   (supplierCity)
        WHERE   (p._pid_, m._mid_) = (pc._pid_, pc._mid_)
        )