SQL - se kända bilförare som kör helt andra bilar

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004

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!

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004

Den första delen du börjat med ser bra ut, alltså detta:

SELECT 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 ........

Så fundera på om du kommer till t.ex. Lisa, vilka bilar ska då vara istället för punkterna? Svaret är: de bilar som Lisa inte kör. Vilket är:

SELECT DISTINCT drives_car.car_id FROM drivers INNER JOIN known_drivers ON known_drivers.driver_id = drivers.driver_id INNER JOIN drives_car ON drives_car.driver_id = known_drivers.driver_id AND NOT drives_car.driver_id = ...... 'Lisas ID

Som du ser saknas det en sak där på slutet, du får själv klura ut hur du får dit Lisas id

Trädvy Permalänk
Medlem
Plats
Västerås
Registrerad
Jun 2005
Citat:

Ursprungligen inskrivet av hivemind
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!

Som vanligt ska vi inte hjälpa till med skoluppgifter och läxor. Utan det får du ordna på egen hand.

Moderator bör låsa tråden!

Fractal Design Arc Svart | MSI Z68A-GD55 G3 REV B3 | Intel® Core i7 2600K, 3.4GHz, 8MB | Corsair 16GB (4x4096MB) CL9 1600Mhz VENGEANCE LP | MSI GeForce GTX 670 | Phanteks PH-TC14PE CPU Cooler (vit) | Corsair Power Supply 650W TX M, Modular, ATX, PS/2 | SSD (okänd tillverkare) + 2 äldre SATA2 diskar på 750 Gb, 350 gb. | OS: Microsoft Windows 10 home.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004

1. Det är ingen läxa, jag försöker fräscha upp mina SQL-kunskaper.
2. Även om det vore läxa är det skillnad på att be någon annan göra dem åt dig och fråga om hjälp på en specifik del.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004
Citat:

Ursprungligen inskrivet av azoapes
Den första delen du börjat med ser bra ut, alltså detta:

SELECT 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 ........

Så fundera på om du kommer till t.ex. Lisa, vilka bilar ska då vara istället för punkterna? Svaret är: de bilar som Lisa inte kör. Vilket är:

SELECT DISTINCT drives_car.car_id FROM drivers INNER JOIN known_drivers ON known_drivers.driver_id = drivers.driver_id INNER JOIN drives_car ON drives_car.driver_id = known_drivers.driver_id AND NOT drives_car.driver_id = ...... 'Lisas ID

Som du ser saknas det en sak där på slutet, du får själv klura ut hur du får dit Lisas id

Hmm, jag får inte till det riktigt, "SQL kompilatorn" klagar ofta på att den inte känner igen olika kolumner.

Jag mixtrade till denna i SQL Server 2008:

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 dc.car_id FROM drivers INNER JOIN known_drivers ON known_drivers.driver_id = drivers.driver_id AND NOT dc.driver_id = d.driver_id); /* driver_id name ----------- -------------------------------- 1 Hanna 2 Lisa 4 Lasse */

Men den ger som synes för många träffar (antar för att jag inte kunde peka ut kolumnerna på det sätt jag ville och få det att kompilera).
Noterbart är att MySQL rapporterar följande fel för exakt samma fråga:

ERROR 1054 (42S22): Unknown column 'dc.driver_id' in 'on clause'

azoapes, någon ytterligare ledtråd? Vilken DBMS kör du med?

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004

Hm... prova byta ut AND mot WHERE då... lite långsammare, men krävs ibland (beror på ordningen som SQL-motorn gör saker i)

Ser ut som att du använder fel driver_id i din subquery. Det ska väl vara known_drivers_driver_id och inte dc.driver_id?

Prova att välja ett ID (t.ex. Lisas ID) och kör bara den där subqueryn. Det är alltid bra att köra ens subquery ensam, för att se att den returnerar rätt resultat. Typ såhär:

SELECT DISTINCT dc.car_id FROM drivers INNER JOIN known_drivers ON known_drivers.driver_id = drivers.driver_id WHERE NOT known_drivers.driver_id = 43

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004

Jo, det är mycket troligt att jag använder fel kolumner på en del ställen.
Hade problem att få frågan att ens köras av DBMS:en. Jag ska nu följa ditt råd och angripa subfrågan separat, dvs, jag ska försöka skriva en fråga som returnerar alla distinkta car_id:n som körs av de personer en given förare känner. Så för Hanna (som har driver_id 1) som känner Pelle och Lisa ska alltså de distinkta car_id:na 2 och 3 returneras. Just nu får jag tillbaka 1, 2, 3, 4 så nåt är skumt men jag ska fila på det och jag skriver här lite senare.

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004
Citat:

Ursprungligen inskrivet av hivemind
Jo, det är mycket troligt att jag använder fel kolumner på en del ställen.
Hade problem att få frågan att ens köras av DBMS:en. Jag ska nu följa ditt råd och angripa subfrågan separat, dvs, jag ska försöka skriva en fråga som returnerar alla distinkta car_id:n som körs av de personer en given förare känner. Så för Hanna (som har driver_id 1) som känner Pelle och Lisa ska alltså de distinkta car_id:na 2 och 3 returneras. Just nu får jag tillbaka 1, 2, 3, 4 så nåt är skumt men jag ska fila på det och jag skriver här lite senare.

Precis, det är rätt väg att gå! När du fått till rätt resultat i din subquery sätter du in den i hela queryn och kollar om det blir rätt. Ett tips är också att populera med mer data, typ 10 förare, för ju mer data desto lättare får du att se vad som kan vara fel i resultatet.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004
Citat:

Ursprungligen inskrivet av azoapes
Precis, det är rätt väg att gå! När du fått till rätt resultat i din subquery sätter du in den i hela queryn och kollar om det blir rätt. Ett tips är också att populera med mer data, typ 10 förare, för ju mer data desto lättare får du att se vad som kan vara fel i resultatet.

Ah, det kanske kan vara en idé, ja. När jag skrev min OP försökte jag minimera storleken på datamängden och ta bort onödiga kolumner för att göra problemet med överskådligt för en tredje part men det är nog läge att lägga in lite fler förare, bilmärken och relationer mellan dessa, ja. Skriver här senare, som sagt, och tusen tack för dina svar änsålänge!

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Dec 2004

Ok, vill man veta vilka distinkta bilar som körs av förare som förare med driver_id 1 (Hanna) känner kan man göra så här:

SELECT DISTINCT drives_car.car_id FROM known_drivers INNER JOIN drives_car ON drives_car.driver_id = known_drivers.known_driver WHERE known_drivers.driver_id = 1;

Men nu vill jag ju använda det i en subfråga för att kunna besvara frågan i min OP och då blir nåt skumt...jag var lite osäker på hur jag skulle skriva när jag inte har ett hårdkodat driver_id. Här är två felaktiga varianter:

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 drives_car.car_id FROM known_drivers INNER JOIN drives_car ON drives_car.driver_id = known_drivers.known_driver WHERE known_drivers.driver_id <> d.driver_id); driver_id name ----------- -------------------------------- 4 Lasse 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 drives_car.car_id FROM known_drivers INNER JOIN drives_car ON drives_car.driver_id = known_drivers.known_driver WHERE known_drivers.driver_id = d.driver_id); driver_id name ----------- -------------------------------- 1 Hanna 2 Lisa 4 Lasse

Vad är fel?