SQL query zoeken naar resultaten met data uit nog een andere tabel

[pilootnour5]

Beste helpmij'ers

Ik zit met dit (voor mij nog) ingewikkelde query-probleem. Om het makkelijker uit te kunnen leggen hierbij twee 'voorbeeldtabellen' waarna ik vervolgens het probleem uitleg.

Tabel: instellingen
[table="width: 500"]
[tr]
[td]user_id[/td]
[td]apk[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]3[/td]
[td]0[/td]
[/tr]
[/table]

Tabel: autos
[table="width: 500"]
[tr]
[td]user_id[/td]
[td]auto_id[/td]
[td]kenteken[/td]
[/tr]
[tr]
[td]1[/td]
[td]7[/td]
[td]1-ABC-23[/td]
[/tr]
[tr]
[td]2[/td]
[td]10[/td]
[td]2-XYZ-88[/td]
[/tr]
[tr]
[td]3[/td]
[td]13[/td]
[td]7-HPM-64[/td]
[/tr]
[/table]

Toelichting:
In de tabel instellingen staat de voorkeur van de gebruiker of zij een APK herinnering willen krijgen. Wanneer 'apk' gelijk is aan 1, dan zouden zij een herinnering willen krijgen. Anders is de waarde 0.

In de tabel autos staan de auto's van de gebruikers. Een gebruiker kan meerdere auto's hebben.

Wat wil ik?
Ik zoek een query waarin ik alle kentekens kan oproepen met bijbehorende auto_id als de eigenaar in 'instellingen' APK op 1 heeft staan bij zijn of haar auto. Het resultaat zou dus dit moeten zijn:

[table="width: 500"]
[tr]
[td]kenteken[/td]
[td]auto_id[/td]
[td]user_id[/td]
[/tr]
[tr]
[td]1-ABC-23[/td]
[td]7[/td]
[td]1[/td]
[/tr]
[tr]
[td]2-XYZ-88[/td]
[td]10[/td]
[td]2[/td]
[/tr]
[/table]

Is dit mogelijk middels één query? Ik maak gebruik van MySQL. Ik hoop dat het verhaal een beetje duidelijk is. Zo niet, dan hoor ik het graag. Alvast bedankt!

 

[OctaFish]

Dat is een vrij simpele query, die er zo uit ziet in SQL:

SELECT kenteken, Autos.auto_id, Autos.user_id FROM Instellingen INNER JOIN Autos ON Instellingen.user_id = Autos.user_id WHERE apk=1;

 

[OctaFish]

Of de variant zonder Join:

SELECT kenteken, Autos.auto_id, Autos.user_id FROM Autos, Instellingen WHERE (Autos.user_id=[Instellingen].[user_id] AND Instellingen.apk=1)

 

[pilootnour5]

Hoi OctaFish. Bedankt voor je antwoord! Je eerste optie heeft de juiste oplossing geboden inderdaad. Erg bedankt! In principe is het goed zo, maar ik wilde nog even ingaan op die tweede query. Bij mij krijg ik bij die query een error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[Instellingen].[user_id] AND Instellingen.apk=1) LIMIT 0, 30' at line 3

Waar zit de fout hier? Wil er graag ook wat van leren dus vanwaar de error?

 

[OctaFish]

Ik zou je graag helpen, maar dit dialect van SQL is voor mij ook enigszins onbekend. Ik heb de query in een Access database gemaakt waarin je redelijk standaard SQL kunt gebruiken. Blijkbaar heeft Maria een afwijkende smaak .
Wél kan ik je uitleggen wat het algemene verschil is.
De eerste variant koppelt de twee tabellen met een Join. Daarmee leg je de verbinding vast tussen de tabel Instellingen en Autos. Het is een Inner Join, wat zoveel betekent als dat je alleen records ziet die in beide tabellen voorkomen. Daarnast heb je nog twee Outer Joins: een Right Outer Join en een Left Outer Join. In het eerste geval zie je alle instellingen, en uit Autos de gekoppelde autos, en in het andere geval precies omgekeerd. Doorgaans is de omgekeerde variant hetzelfde als een Inner Join, want je zult als het goed is geen autos hebben die geen User_ID hebben, dus als je alle autos ziet, heb je ook alle Users te pakken. In het eerste geval zie je dus wél wat anders, want je ziet nu ook de Users die geen auto hebben.

OK, dat is de Join variant. Als je geen Join gebruikt, maar beide tabellen gebruikt en alleen het filter [APK]=1, dan weet de query dus niet welke auto bij welke user hoort. Gevolg is, dat de query alle records uit Instellingen gaat combineren met alle auto's. In jouw voorbeeld heb je 3 users en 3 autos, maar stel dat je 6 users hebt en 4 auto's, dan pakt de query a.h.w. de eerste user, en maakt dan 4 records aan; voor elke auto 1. Daarna de volgende user: ook weer 4 auto's. In totaal krijg je dus 24 records te zien (6 (users) * 4 (auto's) = 24. Zo'n query heeft ook een naam: een Cartesisch Product.

Kan handig zijn (ik gebruik ze regelmatig) maar je wilt hem vaak toch filteren op zinvolle combinaties. In dit geval kun je het veld UserID gebruiken om te matchen, omdat dat veld in beide tabellen voorkomt. En dat doet dus de tweede variant: hij filtert de eerste tabel op basis van het User_ID uit de tweede tabel. In beginsel zou dit dus moeten werken, want is redelijk standaard in een query. Misschien moet je een beetje met de syntax klooien om het goed te krijgen. Je kunt eens proberen de rechte haken weg te laten; Access zet die er standaard altijd bij, maar wellicht dat Maria niet van rechte haken houdt . De eerste variant bevat ze ook niet. Je krijgt dan dit:

SELECT kenteken, Autos.auto_id, Autos.user_id FROM Autos, Instellingen WHERE (Autos.user_id=Instellingen.user_id AND Instellingen.apk=1)

 

[pilootnour5]

Geweldig dit! Je hebt me ontzettend verder geholpen hiermee! De uitleg is erg duidelijk (en herkenbaar met die 24 auto's ) en je laatste query werkte inderdaad goed! Super bedankt!