Joinen 5 tabellen
Ik probeer 5 tabellen te join dmv een query, helaas krijg ik een vage foutmelding, nl dat users tabel niet bestaat. Deze tabellen zijn verbonden dmv een foreign key constrain en het zijn allemaal one-to-many relaties. Vandaar dat het ook 5 tabellen zijn geworden.
De ERD voor normalisatie ziet als volgt uit:
http://dev.pc-on-rails.nl/test2/database_design.png
En mijn query ziet als volgt uit:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT users.user_id,users.firstname,users.lastname,users.pharmacycompany,medicines.name,medicines.price,medicines.production_date,medicines.expire_date,medicinetype.name,suppliers.name
FROM users,medicines,medicinetype,suppliers,medicine_medicinetype_tbl
JOIN users ON medicines.user_id = users.user_id
JOIN medicines ON medicine_medicinetype_tbl.medicine_id=medicines.medicine_id
JOIN medicinetype ON medicine_medicinetype_tbl.medicinetype_id=medicinetype.medicinetype_id
JOIN medicines ON medicine_supplier.medicine_id=medicines.medicine_id
JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
FROM users,medicines,medicinetype,suppliers,medicine_medicinetype_tbl
JOIN users ON medicines.user_id = users.user_id
JOIN medicines ON medicine_medicinetype_tbl.medicine_id=medicines.medicine_id
JOIN medicinetype ON medicine_medicinetype_tbl.medicinetype_id=medicinetype.medicinetype_id
JOIN medicines ON medicine_supplier.medicine_id=medicines.medicine_id
JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
Zoals ik eerder aangaf, krijg ik een vage foutmelding bij het uitvoeren van deze query, nl: #1066 - Not unique table/alias: 'users'. En dit klopt niet, want de tabel users bestaat wel degelijk.
Alvast bedankt en ik hoor graag jullie feedback.
In de query heeft slechts een table de goede naam.
Je kunt er zeker van zijn dat de tabelnamen goed zijn, want dat de tabelnamen heb ik inmiddels aangepast.
Zie het nieuwe plaatje: http://dev.pc-on-rails.nl/test2/database_tabellen.png
Echter, ik vraag me af of de tabel medicinetype.medicinetype_id geen PK moet bevatten, daar deze enkel twee FK heeft met als eigenschap index...
Volgens mij kan ik beide FK aangeven als PK toch of kan ik beter deze tabel een eigen PK geven en twee andere FK gewoon zo laten met hun index eigenschap?
Alvast bedankt voor jullie suggesties/feedback
Gewijzigd op 05/12/2016 08:36:51 door Mohamed nvt
De melding zegt ook niet dat hij niet bestaat.
Die melding zegt dat hij dubbel (niet uniek) bestaat.
<edit> (in je query)</edit>
En dat klopt, want je noemt hem 2x in je query.
SELECT users.user_id,users.firstname,users.lastname,users.pharmacycompany,medicines.name,medicines.price,medicines.production_date,medicines.expire_date,medicinetype.name,suppliers.name
FROM users,medicines,medicinetype,suppliers,medicine_medicinetype_tbl
JOIN users ON medicines.user_id = users.user_id
JOIN medicines ON medicine_medicinetype_tbl.medicine_id=medicines.medicine_id
JOIN medicinetype ON medicine_medicinetype_tbl.medicinetype_id=medicinetype.medicinetype_id
JOIN medicines ON medicine_supplier.medicine_id=medicines.medicine_id
JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
door achter FROM meerdere tabellen te noemen, doe je hetzelfde als
FROM x
JOIN y
JOIN z
(met een enorme resultset als resultaat).
JE moet iets doen als
SELECT...
FROM users
JOIN medicines ON medicines.user_id = users.user_id
Waarbij de tabellen die achter ON staan dus vooraf ook al in een FROM of JOIN regel staan.
TIP
bouw dit langzaam op door eerst de query uit te voeren met alleen een FROM
Daarna voeg je steeds een keer een JOIN toe en check je de query.
Gewijzigd op 05/12/2016 09:46:11 door Ivo P
Heel erg bedankt voor je feedback.
Ik heb jouw advies opgevolgd en stap voor stap de query opgebouwd, totdat alle tabellen waren opgenomen.
Ik moet zeggen dat het me nu ook duidelijk is hoe de query opgebouwd moet worden., Tnx daarvoor.
De query ziet er als volgt uit:
Code (php)
1
SELECT users.firstname,medicines.name,medicines.price,medicines.expire_date,suppliers.name,suppliers.phonenumber,medicinetype.name FROM medicines JOIN users ON medicines.user_id = users.user_id JOIN medicine_supplier ON medicine_supplier.medicine_id=medicines.medicine_id JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id JOIN medicine_medicinetype ON medicine_medicinetype.medicine_id=medicines.medicine_id JOIN medicinetype ON medicine_medicinetype.medicinetype_id=medicinetype.medicinetype_id
Het probleem waar ik nu tegenloop is dat enkel een regel uit de tabel wordt weergegeven i.p.v. de hele tabel.
Wat ik moet nu verder aan de query veranderen om de gehele tabel weer te geven of voor enkel een gebruiker?
Volgens mij moet ik iets doen met group of zo..
Alvast bedankt voor je feedback.
http://wiki.pfz.nl/group-by
je zult als je iets van 1 gebruiker wilt zien een WHERE user.user_id = 1234
of iets dergelijks moeten opnemen
(dat komt dan voorbij de JOIN regels)
PS:
je query is zo op 1 regel geplempt nogal onleesbaar.
Als je op strategische plaatsen eens op enter drukt, dan wordt het vast een stuk overzichtelijker
Mohamed nvt op 05/12/2016 20:15:26:
De query ziet er als volgt uit:
Het probleem waar ik nu tegenloop is dat enkel een regel uit de tabel wordt weergegeven i.p.v. de hele tabel.
Code (php)
1
SELECT users.firstname,medicines.name,medicines.price,medicines.expire_date,suppliers.name,suppliers.phonenumber,medicinetype.name FROM medicines JOIN users ON medicines.user_id = users.user_id JOIN medicine_supplier ON medicine_supplier.medicine_id=medicines.medicine_id JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id JOIN medicine_medicinetype ON medicine_medicinetype.medicine_id=medicines.medicine_id JOIN medicinetype ON medicine_medicinetype.medicinetype_id=medicinetype.medicinetype_id
Het probleem waar ik nu tegenloop is dat enkel een regel uit de tabel wordt weergegeven i.p.v. de hele tabel.
Als je de query inderdaad even uitschrijft, valt op dat je van FROM users bent overgestapt op FROM medicines:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
users.firstname,
medicines.name,
medicines.price,
medicines.expire_date,
suppliers.name,
suppliers.phonenumber,
medicinetype.name
FROM medicines
JOIN users ON medicines.user_id = users.user_id
JOIN medicine_supplier ON medicine_supplier.medicine_id=medicines.medicine_id
JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
JOIN medicine_medicinetype ON medicine_medicinetype.medicine_id=medicines.medicine_id
JOIN medicinetype ON medicine_medicinetype.medicinetype_id=medicinetype.medicinetype_id
users.firstname,
medicines.name,
medicines.price,
medicines.expire_date,
suppliers.name,
suppliers.phonenumber,
medicinetype.name
FROM medicines
JOIN users ON medicines.user_id = users.user_id
JOIN medicine_supplier ON medicine_supplier.medicine_id=medicines.medicine_id
JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
JOIN medicine_medicinetype ON medicine_medicinetype.medicine_id=medicines.medicine_id
JOIN medicinetype ON medicine_medicinetype.medicinetype_id=medicinetype.medicinetype_id
Wat wil je precies weergeven: per gebruiker al zijn/haar medicijnen of per medicijn alle gebruikers?
Als je eventueel alle gebruikers plus alle medicijnen wilt weergeven, moet je het type join aanpassen. Cheat sheet:
Heel erg bedankt voor jullie feedback.
Initieel wilde ik testen/leren om de inhoud van alle gegevens weer te geven, vandaar ook mijn vraag. Later wilde ik verder gaan om alle gegevens gerelateerd aan een gebruiker weer te geven.
Beide taken lukt het nu wel.
Om alle gegevens van bijv gebruiker_=1 weer te geven, gebruik ik onderstaande query en het doet precies wat ik wil, nl full join. Dit betekent ook dat er gegevens worden weergegeven die bijv niet macht met andere gegevens uit andere tabellen. Dit is nu niet erg, want uiteindelijk pak ik dit verder wanneer de interface rondom DB klaar is...
SELECT *
FROM users
LEFT JOIN medicines ON users.user_id = medicines.user_id
LEFT JOIN medicine_supplier ON medicine_supplier.medicine_id=medicines.medicine_id
LEFT JOIN suppliers ON medicine_supplier.supplier_id=suppliers.supplier_id
LEFT JOIN medicine_medicinetype ON medicine_medicinetype.medicine_id=medicines.medicine_id
LEFT JOIN medicinetype ON medicine_medicinetype.medicinetype_id=medicinetype.medicinetype_id
WHERE users.user_id=1;
Nogmaals erg bedankt voor het meedenken!