Forum Mjukvara Programmering och digitalt skapande Tråd Inlägg

SQL - skriva om WHERE till JOIN

1
Skriv svar
Permalänk
Medlem
●

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?

Redigera
Citera flera Citera
Permalänk
Medlem
●
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.

Redigera
Citera flera Citera
Permalänk
Medlem
●
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... :/

Redigera
Citera flera Citera
Permalänk
Medlem
●

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

Senast redigerat
Redigera
Citera flera Citera
Permalänk
Medlem
●

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';
Redigera
Citera flera Citera
Permalänk
Medlem
●
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

Redigera
Citera flera Citera
Permalänk
Medlem
●

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?

Redigera
Citera flera Citera
Permalänk
Medlem
●
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.

Senast redigerat
Redigera
Citera flera Citera
Permalänk
Medlem
●

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?

Redigera
Citera flera Citera
Permalänk
Medlem
●

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'
Redigera
Citera flera Citera
Permalänk
Medlem ★
●

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'
Visa signatur

AMD 7700X (EK 240mm AIO) | ROG Strix B650E-F Gaming | Gigabyte RX 6800 XT 16GB OC | Kingston Fury 32GB DDR5 5600mhz | Kingston Fury Renegade M2 2TB | Alienware AW2723DF 280hz

Redigera
Citera flera Citera
Permalänk
Medlem
●

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!

Redigera
Citera flera Citera
1
Skriv svar
Senaste nyheterna
Hårdvara
Mjukvara
Övrigt
Nytt i forumet
Datorkomponenter
System
Ljud, bild och kommunikation
Spel och mjukvara