How to use INNER LEFT instead of EXCEPT?

I read that MySQL does not support EXCEPT, and the workaround is to use LEFT JOIN.

THIS IS MY QUERY:

(SELECT ManufacturerID FROM ComputerManufacturers INNER JOIN Computers ON 
(Computers.ID = ComputerManufacturers.ComputerID AND Computers.IsLaptop = 1))
EXCEPT
(SELECT ManufacturerID FROM ComputerManufacturers INNER JOIN Computers ON 
(Computers.ID = ComputerManufacturers.ComputerID AND Computers.IsLaptop = 0));

Basically: Trying to find out the manufacturers that sell PCs but not laptops.

How can I convert this query with the LEFT JOIN? I got confused..

Table Computers:

+----+-------+----------------+-----+----------+----------+------------------+---------+-------+
| ID | Model | ProcessorSpeed | RAM | HDDSpace | IsLaptop | LaptopScreenSize | CDSpeed | Price |
+----+-------+----------------+-----+----------+----------+------------------+---------+-------+
|  1 |  2001 |            200 |  50 |       10 |        0 |             NULL |      12 |  1500 |
|  2 |  2002 |           2000 |  15 |       10 |        0 |             NULL |      12 |   400 |
|  3 |  2003 |           2000 |  50 |        8 |        0 |             NULL |      24 |   500 |
|  4 |  2004 |           2000 |  15 |        4 |        1 |               18 |     600 |  1400 |
+----+-------+----------------+-----+----------+----------+------------------+---------+-------+

Table Manufacturers Computers:

+----+----------------+------------+
| ID | ManufacturerID | ComputerID |
+----+----------------+------------+
|  1 |              2 |          1 |
|  2 |              1 |          2 |
|  3 |              2 |          3 |
|  4 |              1 |          4 |
+----+----------------+------------+

So since Manufacturer ID number 1 sells Laptop the result set should include only Manufacturer ID number 2.

Answer

SELECT ComputerManufacturers.ManufacturerID 
FROM ComputerManufacturers 
INNER JOIN Computers ON Computers.ID = ComputerManufacturers.ComputerID 
GROUP BY 1
HAVING MIN(Computers.IsLaptop) = 1

Advertisement

Answer