SQL - skriva om WHERE till JOIN

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

SQL - skriva om WHERE till JOIN

Hej, jag har följande tabeller:

CREATE TABLE expert( id INT PRIMARY KEY, name VARCHAR(128) NOT NULL); CREATE TABLE expertise_area( id INT PRIMARY KEY, topic VARCHAR(128) NOT NULL, subarea_of INT, CONSTRAINT fk_subarea_of FOREIGN KEY(subarea_of) REFERENCES expertise_area(id)); CREATE TABLE expert_in( expert INT NOT NULL, expertise_area INT NOT NULL, PRIMARY KEY(expert, expertise_area), CONSTRAINT fk_expert_in FOREIGN KEY(expert) REFERENCES expert(id), CONSTRAINT fk_expertise_area FOREIGN KEY(expertise_area) REFERENCES expertise_area(id));

Nu vill jag veta vilka personer som är experter i flugfisker, då kan man göra:

SELECT expert.name FROM expert, expert_in, expertise_area WHERE expert.id=expert_in.expert AND expert_in.expertise_area = expertise_area.id AND expertise_area.topic = 'flugfiske';

Med querien ovan får jag korrekt resultat, endast de experter som är listade som är experter i flugfiske returneras. Jag får problem när jag försöker skriva om frågan med joins istället:

SELECT expert.name FROM (expert INNER JOIN expert_in ON expert.id=expert_in.expert INNER JOIN expertise_area ON expert_in.expertise_area=expertise_area.id INNER JOIN expertise_area AS ea ON ea.topic = 'flugfiske');

Denna verkar returnera alla experter i expert_in-tabellen inte bara de som är experter i flugfiske. Om jag ändrar ändrar flugfiske till nåt som inte finns så returneras inga träffar och det är som det ska vara, men ändå får jag med träffar som är felaktiga.

Hur ska jag skriva om JOIN-frågan så den beter korrekt som WHERE-varianten?

Trädvy Permalänk
Medlem
Plats
Västerås
Registrerad
Maj 2007

SELECT expert.name FROM (expert INNER JOIN expert_in ON expert.id=expert_in.expert INNER JOIN expertise_area ON expert_in.expertise_area=expertise_area.id WHERE expertise_area.topic = 'flugfiske');

Den borde göra det du söker efter.

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

Ursprungligen inskrivet av Dynamo

SELECT expert.name FROM (expert INNER JOIN expert_in ON expert.id=expert_in.expert INNER JOIN expertise_area ON expert_in.expertise_area=expertise_area.id WHERE expertise_area.topic = 'flugfiske');

Den borde göra det du söker efter.

Använder MySQL 5.1.35, och får följande fel när jag testade den:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE expertise_area.topic = 'flugfiske')' at line 4

Inte särskilt bra ledtråd vad som är fel... :/

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

Vid en omskriving:

SELECT expert.name FROM expert, expertise_area INNER JOIN expert_in ON expert_in.expert_id=expert.id INNER JOIN expertise_area AS ea ON expert_in.expertise_area=ea.id WHERE expertise_area='flugfiske';

blir felet istället:

ERROR 1054 (42S22): Unknown column 'expert.id' in 'on clause'

Hmm?

Edit: Fixad kod, samma fel

Trädvy Permalänk
Medlem
Plats
Västerås
Registrerad
Maj 2007

Tittade inte igenom dina Join-satser riktigt innan, denna bör fungera

SELECT expert.name FROM expert INNER JOIN expert_in ON expert_in.expert = expert.id INNER JOIN expertise_area ON expert_in.expertise_area = expertise_area.id WHERE expertise_area.topic = 'flugfiske';

Trädvy Permalänk
Medlem
Plats
Karlstad
Registrerad
Apr 2004

SELECT expert.name FROM expert INNER JOIN expert_in ON expert.id=expert_in.expert INNER JOIN expertise_area ON expert_in.expertise_area=expertise_area.id WHERE expertise_area.topic = 'flugfiske';

Detta kanske fungerar bättre.

Edit: too late

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

Båda dom ger följande fel:

ERROR 1054 (42S22): Unknown column 'expert_in.expert' in 'on clause'

och kastar man om ordningen får man felet jag visade i min förra post.
Idéer?

Trädvy Permalänk
Medlem
Plats
Laholm
Registrerad
Okt 2005

CREATE TABLE expert( id INT PRIMARY KEY, name VARCHAR(128) NOT NULL); CREATE TABLE expertise_area( id INT PRIMARY KEY, topic VARCHAR(128) NOT NULL, subarea_of INT, CONSTRAINT fk_subarea_of FOREIGN KEY(subarea_of) REFERENCES expertise_area(id)); CREATE TABLE expert_in( expert INT NOT NULL, expertise_area INT NOT NULL, PRIMARY KEY(expert, expertise_area), CONSTRAINT fk_expert_in FOREIGN KEY(expert) REFERENCES expert(id), CONSTRAINT fk_expertise_area FOREIGN KEY(expertise_area) REFERENCES expertise_area(id)); SELECT expert.name FROM expert, expert_in, expertise_area WHERE expert.id=expert_in.expert AND expert_in.expertise_area = expertise_area.id AND expertise_area.topic = 'flugfiske'; -- Prova detta... SELECT `e`.`name` FROM `expert_in` AS `ei` LEFT JOIN `expert` AS `e` ON `e`.`id` = `ei`.`expert` LEFT JOIN `expertise_area` AS `ea` ON `ea`.`id` = `ei`.`expertise_area` WHERE `ea`.`topic` = 'flugfiske';

Jag provade med exakt samma struktur för tabellerna och det fungerade för mig. Så dra en copy-paste på den SQL:en så ska det funka.

ASP.NET programmerare i C#
Twitter: http://www.twitter.com/hagbarddenstore

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

Hagbarddenstore, i vilken databas gjorde du det i? För med den får jag:

ERROR 1054 (42S22): Unknown column 'ei.expert' in 'on clause'

i MySQL 5.1.35.

Varför använder du

`

så intensivt förresten?

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

Här är ett komplett script förresten:

DROP TABLE IF EXISTS expert_in; DROP TABLE IF EXISTS expertise_area; DROP TABLE IF EXISTS expert; CREATE TABLE expert( id INT PRIMARY KEY, name VARCHAR(128) NOT NULL); CREATE TABLE expertise_area( id INT PRIMARY KEY, topic VARCHAR(128) NOT NULL, subarea_of INT, CONSTRAINT fk_subarea_of FOREIGN KEY(subarea_of) REFERENCES expertise_area(id)); CREATE TABLE expert_in( expert_id INT NOT NULL, expertise_area INT NOT NULL, PRIMARY KEY(expert_id, expertise_area), CONSTRAINT fk_expert_in FOREIGN KEY(expert_id) REFERENCES expert(id), CONSTRAINT fk_expertise_area FOREIGN KEY(expertise_area) REFERENCES expertise_area(id)); INSERT INTO expert (id, name) VALUES (1, 'Lisa'), (2, 'Hanna'), (3, 'Pelle'), (4, 'Richard'), (5, 'Christopher'), (6, 'Mr T'), (7, 'Stina'); INSERT INTO expertise_area (id, topic, subarea_of) VALUES (1, 'naturaktiviteter', NULL), (2, 'fiske', 1), (3, 'flugfiske', 2), (4, 'fordon', NULL); INSERT INTO expert_in (expert_id, expertise_area) VALUES (2, 3), (7, 3), (1, 4); -- Hanna and Stina are experts in databases. SELECT expert.name FROM expert, expert_in, expertise_area WHERE expert.id=expert_in.expert_id AND expert_in.expertise_area = expertise_area.id AND expertise_area.topic = 'flugfiske'; SELECT `e`.`name` FROM `expert_in` AS `ei` LEFT JOIN `expert` AS `e` ON `e`.`id` = `ei`.`expert` LEFT JOIN `expertise_area` AS `ea` ON `ea`.`id` = `ei`.`expertise_area` WHERE `ea`.`topic` = 'flugfiske'; SELECT expert.name FROM expert INNER JOIN expert_in ON expert.id=expert_in.expert INNER JOIN expertise_area ON expert_in.expertise_area=expertise_area.id WHERE expertise_area.topic = 'flugfiske';

Och utmatningen:

Query OK, 0 rows affected (0.08 sec) Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.13 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.17 sec) Query OK, 7 rows affected (0.04 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 +-------+ | name | +-------+ | Hanna | | Stina | +-------+ 2 rows in set (0.00 sec) ERROR 1054 (42S22): Unknown column 'ei.expert' in 'on clause' ERROR 1054 (42S22): Unknown column 'expert_in.expert' in 'on clause'

Trädvy Permalänk
Medlem
Plats
Köpenhamn
Registrerad
Nov 2005

Jag jobbar inte med mysql utan mssql server. Men CREATE TABLE skiljer sig som tur är inte.

Jag hittar inte kolumnen [expert] i [expert_in]-tabellen som du försöker joina på. Så felet är inget konstigt.
Du vet att du kan lägga WHERE-villkoret på ON CLAUSE:n likaså, för att slippa WHERE-satsen helt.

Du vill nog ha något i stil med..

SELECT expert.name FROM expert AS E INNER JOIN expert_in AS EI ON E.id=EI.expert_id INNER JOIN expertise_area AS EA ON EI.expertise_area=EA.id AND EA.topic = 'flugfiske'

^__^

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

Tack för svaret, Oggelito! Jag missade att jag bytt namn på en kolumn där.

Nu kunde jag fixa den förra:

SELECT `e`.`name` FROM `expert_in` AS `ei` LEFT JOIN `expert` AS `e` ON `e`.`id` = `ei`.`expert_id` LEFT JOIN `expertise_area` AS `ea` ON `ea`.`id` = `ei`.`expertise_area` WHERE `ea`.`topic` = 'flugfiske';

och även din fungerar under MySQL med en liten, liten ändring:

SELECT E.name FROM expert AS E INNER JOIN expert_in AS EI ON E.id=EI.expert_id INNER JOIN expertise_area AS EA ON EI.expertise_area=EA.id AND EA.topic = 'flugfiske';

Din var precis vad jag var ute efter, tack så hemskt mycket alla ni som hjälpt till!