SQL - se kända bilförare som kör helt andra bilar
Hej, kolla följande script (som jag provat i MySQL 5.1.35 och Microsoft SQL Server 2008 SP1), där finns min fråga:
CREATE TABLE cars (
car_id INT PRIMARY KEY,
maker VARCHAR(32) NOT NULL);
CREATE TABLE drivers (
driver_id INT PRIMARY KEY,
name VARCHAR(32) NOT NULL);
CREATE TABLE drives_car (
driver_id INT NOT NULL,
car_id INT NOT NULL,
PRIMARY KEY(driver_id, car_id),
FOREIGN KEY(driver_id) REFERENCES drivers(driver_id),
FOREIGN KEY(car_id) REFERENCES cars(car_id));
CREATE TABLE known_drivers (
driver_id INT NOT NULL,
known_driver INT NOT NULL,
PRIMARY KEY(driver_id, known_driver),
FOREIGN KEY(driver_id) REFERENCES drivers(driver_id),
FOREIGN KEY(known_driver) REFERENCES drivers(driver_id));
INSERT INTO cars (car_id, maker) VALUES
(1, 'Mercedes'),
(2, 'BMW'),
(3, 'Volvo'),
(4, 'Toyota');
INSERT INTO drivers (driver_id, name) VALUES
(1, 'Hanna'),
(2, 'Lisa'),
(3, 'Pelle'),
(4, 'Lasse');
-- Hanna kör Mercedes och Volvo, Lisa kör BMW och Volvo,
-- Pelle kör BMW och Lasse kör Mercedes och Toyota.
INSERT INTO drives_car (driver_id, car_id) VALUES
(1, 1),
(1, 3),
(2, 2),
(2, 3),
(3, 2),
(4, 1),
(4, 4);
-- Hanna känner Lisa och Pelle, Lisa känner Pelle och
-- Lasse känner de andra tre.
INSERT INTO known_drivers (driver_id, known_driver) VALUES
(1, 2),
(1, 3),
(2, 3),
(4, 1),
(4, 2),
(4, 3);
/* Förare och vilka bilmärken de kör:
SELECT drivers.name, cars.maker FROM drivers, drives_car, cars
WHERE drivers.driver_id = drives_car.driver_id AND drives_car.car_id = cars.car_id
ORDER BY name;
+-------+----------+
| name | maker |
+-------+----------+
| Hanna | Mercedes |
| Hanna | Volvo |
| Lasse | Toyota |
| Lasse | Mercedes |
| Lisa | Volvo |
| Lisa | BMW |
| Pelle | BMW |
+-------+----------+
7 rows in set (0.32 sec)
*/
/* Vilka förare känner en given förare?
SELECT d1.name AS 'driver', d2.name AS 'known driver' FROM drivers AS d1, drivers AS d2, known_drivers
WHERE d1.driver_id = known_drivers.driver_id AND d2.driver_id = known_drivers.known_driver
ORDER BY d1.name;
+--------+--------------+
| driver | known driver |
+--------+--------------+
| Hanna | Pelle |
| Hanna | Lisa |
| Lasse | Hanna |
| Lasse | Pelle |
| Lasse | Lisa |
| Lisa | Pelle |
+--------+--------------+
6 rows in set (0.02 sec)
*/
-- Nu vill vi veta: Vilka bilförare x känner en bilförare som
-- kör helt andra bilar än x kör? Det får inte finnas något
-- överlapp.
-- Hanna kör Mercedes och Volvo och känner Lisa och Pelle. Lisa
-- kör BMW och Volvo och Pelle kör BMW. Hanna är alltså en träff
-- eftersom hon känner Pelle som kör helt andra bilar.
-- Vi ska även få träff på Lasse, som känner alla andra förare och kör
-- Mercedes och Toyota, eftersom han har inget överlapp mot vare
-- sig Lisa eller Pelle.
-- Följande fråga är helt fel (ger inga träffar). Jag har testat några
-- varianter som antingen ger inga eller för många träffar.
-- Hur ska man göra??
SELECT DISTINCT d.driver_id, d.name FROM drivers AS d
INNER JOIN known_drivers AS kd ON kd.driver_id = d.driver_id
INNER JOIN drives_car AS dc ON dc.driver_id = kd.driver_id
WHERE dc.car_id NOT IN
(SELECT DISTINCT cars.car_id FROM drivers, cars, known_drivers
WHERE drivers.driver_id = known_drivers.known_driver AND
dc.car_id = cars.car_id);
Det jag undrar är alltså hur jag ska skriva sista frågan för att få de resultat jag vill? Jag vet att jag är helt fel ute nu men jag hoppas det är nåt att utgå ifrån iallafall. Jag postade hela scriptet så andra snabbt ska kunna skapa denna lilla databas ifall så önskas.
Tacksam för all hjälp!
