[MySQL] Data uit tabellen gebruiken in een koppeltabel

[F.West98]

Inkoppertje, dat is makkelijk met INSERT INTO SELECT en SELECT INTO.

Maar wat nou als de info uit meerdere tabellen moet komen (onafhankelijke tabellen, want je wil iets in de koppeltabel zetten) en één van de twee resultaten heeft meerdere resultaten?

Men neme deze opzet:

Personen	Koppel1	Specialiteiten	Koppel2	Categorieen
userID naam ...	userID specialiteitID	specialiteitID naam code	specialiteitID categorieID	categorieID naam

Stel ik maak een persoon en zijn specialiteit aan, en ik koppel die daarna:

SQL:

INSERT INTO `Personen`(`naam`) VALUES('Jantje Klaassen')
INSERT INTO `Specialiteiten`(`naam`, `code`) VALUES('Lopen', 'LO')
/* Ik weet de ID's niet, in de praktijk worden meerdere personen en specialiteiten tegelijk aangemaakt, dus LAST_INSERTED_ID() werkt niet */
INSERT INTO `Koppel1`(`userID`, `specialiteitID`)
    VALUES (
        (SELECT `userID` FROM `Personen` WHERE `naam` = 'Jantje Klaassen'),
        (SELECT `specialiteitID` FROM `Specialiteiten` WHERE `naam` = 'Lopen')
    )

Dat werkt prima.

Maar, nu weet ik niet of de aangeleverde naam bij specialiteit nu de categorienaam, de specialiteitnaam of -code is.
De laatste twee kan makkelijk door de tweede SELECT aan te passen:

SQL:

SELECT `specialiteitID` FROM `Specialiteiten` WHERE `naam` = 'Lopen' OR `code` = 'Lopen')

Aannemende dat de categorieëntabel al gevuld is, en is gekoppeld met specialiteiten had ik dit bedacht voor de SELECT (voor als de aangeleverde naam die van de categorie is)

SQL:

SELECT `specialiteitID` FROM `Specialiteiten`
    WHERE `naam` = 'Lopen' OR `code` = 'Lopen' OR
        `specialiteitID` IN (SELECT `specialiteitID` FROM `Koppel2` INNER JOIN `Categorieen` USING (categorieID) WHERE `Categorieen`.`naam` = 'Lopen')

Dat werkt ook wel leuk, tenzij de categorie meerdere specialiteiten heeft. Dan krijg ik ineens meerdere resultaten uit de query en gaat de insert mis.
Ik wil wel dat dan voor alle items uit die query (dus voor alle specialiteiten in een categorie) een item wordt gemaakt in de koppeltabel.

Hoe moet ik dit aanpassen om dit werkend te krijgen? Ik kom er niet uit

[ Voor 193% gewijzigd door F.West98 op 21-12-2013 22:57 ]

[pedorus]

Zie http://dev.mysql.com/doc/refman/5.5/en/insert-select.html ? En ik neem aan dat je in de select dan een join wil doen. In de TS lijkt een duidelijke uitleg te ontbreken van wat je nu eigenlijk aan proberen bent, dus het zijn vermoedens. last insert id of een orm gebruiken zou wellicht ook een antwoord kunnen zijn.

[ Voor 14% gewijzigd door pedorus op 21-12-2013 23:09 ]

[F.West98]

Misschien idd onduidelijk wat ik precies probeer, maar:
Ik probeer Koppel1 met data te vullen. Ik heb alle namen/codes van de specialiteiten en daarmee kan ik dus gewoon bijbehorende specialiteitID kiezen om te gebruiken.
INSERT INTO SELECT doe ik eigenlijk al, maar omdat de bron waar ik moet zoeken 2 verschillende tabellen zijn, zijn het er dus 2.
LAST_INSERT_ID werkt niet, omdat dit om veel entries gaat, en het anders te lang zou gaan duren. Ik doe alle INSERTS in de Personen tabel dus in één query (met VALUES(...), (...), (...)) en dus gaat dat niet meer op.

Verder kan het zijn dat de data waar ik op zoek OF de specialiteitcode, OF de -naam, OF een categorienaam is.
In dat laatste geval wil ik een item in de koppeltabel Koppel1 maken voor ALLE specialiteiten in die categorie.

[pedorus]

F.West98 schreef op zaterdag 21 december 2013 @ 23:21:
INSERT INTO SELECT doe ik eigenlijk al, maar omdat de bron waar ik moet zoeken 2 verschillende tabellen zijn, zijn het er dus 2.

Misschien helpt het om het process in 2 stappen op te splitsen: Eerst een query maken die retourneerd wat je in de tabel wil stoppen, daarna deze query even omschrijven in een insert.

LAST_INSERT_ID werkt niet, omdat dit om veel entries gaat, en het anders te lang zou gaan duren. Ik doe alle INSERTS in de Personen tabel dus in één query (met VALUES(...), (...), (...)) en dus gaat dat niet meer op.

Je kunt prima 1 prepared statement van het type VALUES (?,?,?,...) gebruiken waarmee je in batch duizend(en) inserts doe en alle gegenereerde ids terugkrijgt. Hangt van de aanroeptaal af hoe je dit precies moet doen. Een ORM regelt dit (soms) ook.

Verder kan het zijn dat de data waar ik op zoek OF de specialiteitcode, OF de -naam, OF een categorienaam is.

Je weet toch wat je aan het doen bent als je daarvoor een insert doet? Maar desnoods kan een UNION of full outer join helpen.. Er lijkt me dan wel iets vreemds aan de hand met de databaseopzet.

[F.West98]

Dat is ook een optie inderdaad
Het lijkt me wel de enige optie, maar volgens mij MOET er een betere optie zijn

En dat laatste: Ja, de OUDE DBopzet is vreselijk brak. De nieuwe niet. Maar dan moet ik die toch nog wel overzetten?

[_js_]

Weet je zeker dat je van mysql meerdere insert ids kunt krijgen uit één query?

Het lijkt me dat het snelheidsprobleem ook wel op andere manieren moet zijn op te lossen, zoals met langere transacties, tijdelijk uitschakelen van indexen of langere locks. En ik betwijfel of de nieuwe manier echt sneller is dan waar je per insert het id terugkrijgt, dat scheelt weer zoeken. Ook zeg je dat dit is om gegevens eenmalig over te zetten, dan lijkt snelheid veel minder van belang.

Om het probleem op te lossen kun je inderdaad zoals pedorus zegt een select met een subselect voor de naam (of last_insert_id() als je toch per naam gaat werken) schrijven waarmee je alle ids terug krijgt ipv values met een paar subselects waarmee je maximaal 1 rij kunt toevoegen.

[Douweegbertje]

Je argumenten zijn vrij slecht. Last insert ID is DE manier om relaties te leggen en domweg zijn er geen andere oplossingen tenzij je 100% zeker weet dat je select (zoals je het nu doet) gegarandeerd uniek is (wat dus vrijwel nooit is).

code:

INSERT INTO `Personen`(`naam`) VALUES('Jantje Klaassen') 

//hier gewoon even insert last id ophalen

INSERT INTO `Specialiteiten`(`naam`, `code`) VALUES('Lopen', 'LO') 

// hier weer last id ophalen.

INSERT INTO `Koppel1`(`userID`, `specialiteitID`) 
    VALUES ( 
        (lastid1'), 
        (lastid2') 
    )

Hier is echt niets raars aan, noch erg intensief oid. Of je nu 2x een SELECT doet of 2x een insert last id.. dan weet ik vrij zeker dat de last id minder resources kost

De vraag is echter waar je dan op vast loopt aangezien je zegt dat last id niet werkt.

Ok, even beter gelezen. Maar in dat geval kun je het nog steeds gebruiken behalve dat last ID altijd de EERSTE insert terug geeft. Daarbij moet je dan even in je code je eigen 'increment' voor lastid toevoegen.

Maar dan nog word het lastig als je hier een voorbeeld neerzet die je niet gebruikt... Waar is dan je query die vast loopt, en moet je die elke keer draaien of pas als een user erbij komt?

[ Voor 19% gewijzigd door Douweegbertje op 22-12-2013 12:55 ]

[F.West98]

@DE: De specialiteiten bestaan allemaal al, dus die gaat niet op

Maar ik heb het nu opgelost door eerst alle specialiteitID's op te halen, en die te gebruiken uiteindelijk. Dat is IETS langzamer, maar scheelt ingewikkelde constructies.

[SPee]

F.West98 schreef op zaterdag 21 december 2013 @ 23:21:
Misschien idd onduidelijk wat ik precies probeer, maar:
Ik probeer Koppel1 met data te vullen. Ik heb alle namen/codes van de specialiteiten en daarmee kan ik dus gewoon bijbehorende specialiteitID kiezen om te gebruiken.
INSERT INTO SELECT doe ik eigenlijk al, maar omdat de bron waar ik moet zoeken 2 verschillende tabellen zijn, zijn het er dus 2.
LAST_INSERT_ID werkt niet, omdat dit om veel entries gaat, en het anders te lang zou gaan duren. Ik doe alle INSERTS in de Personen tabel dus in één query (met VALUES(...), (...), (...)) en dus gaat dat niet meer op.

Verder kan het zijn dat de data waar ik op zoek OF de specialiteitcode, OF de -naam, OF een categorienaam is.
In dat laatste geval wil ik een item in de koppeltabel Koppel1 maken voor ALLE specialiteiten in die categorie.

Hoezo doe je een INSERT INTO SELECT
Er is een verschil met INSERT INTO ... VALUES ( SELECT ...).

Wat je volgens mij wil is een query uitvoeren en alle resultaten invoegen. Dat kan met INSERT INTO SELECT:

SQL:

Insert into koppel1 (`userID`, `specialiteitID`) 
Select
        (SELECT `userID` FROM `Personen` WHERE `naam` = 'Jantje Klaassen'), 
        s.SpecialiteitID
From specialiteiten s
Left join koppel2 using (specialiteitID)
Join categorieen c using (categorieID)
Where s.naam = 'Lopen' or s.code = 'Lopen' or c.naam = 'Lopen'

Je kan deze query testen/finetunen door enkel de select uit te voeren en wanneer je tevreden bent de insert gebruiken. Alle records van de query worden ingevoegd. Zoals je ziet; geen gebruik van VALUES.
En dan is de volgende stap om te zorgen dat er geen dubbele records in komen.

[F.West98]

SPee schreef op dinsdag 24 december 2013 @ 15:58:
[...]


Hoezo doe je een INSERT INTO SELECT
Er is een verschil met INSERT INTO ... VALUES ( SELECT ...).

Wat je volgens mij wil is een query uitvoeren en alle resultaten invoegen. Dat kan met INSERT INTO SELECT:

SQL:

Insert into koppel1 (`userID`, `specialiteitID`) 
Select
        (SELECT `userID` FROM `Personen` WHERE `naam` = 'Jantje Klaassen'), 
        s.SpecialiteitID
From specialiteiten s
Left join koppel2 using (specialiteitID)
Join categorieen c using (categorieID)
Where s.naam = 'Lopen' or s.code = 'Lopen' or c.naam = 'Lopen'

Je kan deze query testen/finetunen door enkel de select uit te voeren en wanneer je tevreden bent de insert gebruiken. Alle records van de query worden ingevoegd. Zoals je ziet; geen gebruik van VALUES.
En dan is de volgende stap om te zorgen dat er geen dubbele records in komen.

Daar wachtte ik op!
Mooi leermoment, ik heb het nu al anders opgelost, maar dit is ook heel goed!

Dubbele records: een unique key over beide kolommen in koppel1.

[BertS]

F.West98 schreef op zaterdag 21 december 2013 @ 23:34:
En dat laatste: Ja, de OUDE DBopzet is vreselijk brak. De nieuwe niet. Maar dan moet ik die toch nog wel overzetten?

Wat is er dan niet brak aan de tabelnaam 'koppel1'

[F.West98]

BertS schreef op donderdag 26 december 2013 @ 19:44:
[...]

Wat is er dan niet brak aan de tabelnaam 'koppel1'

Het feit dat het slechts een voorbeeld is

In het echt gaat het om andere tabellen, andere inhoud, en andere koppelnamen