sql command help........?

Question

Now..i have two table which is car and 4wd...example :


CAR TABLE
Model Price Manufacturer
Waja 50000 Proton
Suria 40000 Naza
MyVi 43000 Perodua


4WD TABLE
Model Price Manufacturer
Kembara 60000 Perodua
Naza Ria 80000 Naza
Land Rover 400000 Land Rover


In using mysql command...
1. How to find the manufacturer who produced both car and 4wd?
2. How to find the manufacturer who only produced car
3. How to find the manufacturer and their model name, who only produced car or 4wd

Answer 1

1

SELECT DISTINCT Manufacturer FROM car WHERE Manufacturer IN (SELECT Manufacturer FROM 4wd)

2

SELECT DISTINCT Manufacturer FROM car WHERE Manufacturer NOT IN (SELECT Manufacturer FROM 4wd)



Jack u are right.. I havent noticed 'only'



3

(SELECT Model, Manufacturer FROM car WHERE Manufacturer NOT IN SELECT Manufacturer FROM 4wd) UNION (SELECT Model, Manufacturer FROM 4wd WHERE Manufacturer NOT IN SELECT Manufacturer FROM car)

Answer 2

Jack basically has it right...I'd only modify his 3rd answer slightly so you knew a little more clearly which table a given row came from:



SELECT manufacturer, model, "Car" FROM carTable

WHERE manufacture NOT IN (SELECT DISTINCT manufacturer FROM 4wdTable)

UNION

SELECT manufacturer, model, "4WD" FROM 4wdTable

WHERE manufacturer NOT IN (SELECT DISTINCT manufacturer FROM carTable)

Answer 3

1) SELECT A.manufacturer FROM CAR_TABLE A INNER JOIN 4WD_TABLE B

ON A.manufacturer=B.manufacturer



2) SELECT A.manufacturer FROM CAR_TABLE A WHERE A.manufacturer NOT IN(SELECT B.manufacturer FROM 4WD_TABLE B)



3) SELECT 1 As INF, A.manufacturer,A.Model FROM CAR_TABLE A WHERE A.manufacturer NOT IN(SELECT B.manufacturer FROM 4WD_TABLE B) UNION ALL SELECT 2 As INF,A.manufacturer,A.Model FROM 4WD_TABLE A WHERE A.manufacturer NOT IN(SELECT B.manufacturer FROM CAR_TABLE)





Allright?







Dear MadProfessor: Take a look at my 3dr query:

1 As INF: Identifies the manufactures which produces only CARS

2 As INF: Identifies the manufactures which produces only 4WD



So, the first column of the results outputs '1' or '2', a number which you can to handle any possibily to filter as you need.