[PHP/MySQL] Selecteren uit relationele database

[babbaloerie]

Misschien een hele simpele vraag, maar ik ben hier al dagen mee bezig, en heb na dagen van googlen en prutsen besloten het toch maar hier neer te zetten in de hoop iets wijzer te worden.

Ik heb de volgende tabellen in mijn mysql database:

user

id	username
1	babbaloerie

user_type

id	user_id	type_id
1	1	1
2	1	2
3	1	3

Ik wil uit de tabel user de rijen selecteren waar type_id (uit user_type) 1 en 2 is.

Nu had ik de volgende query:

code:

SELECT user.id AS id,
user.username AS username,
user_type.type_id AS type_id
FROM `user`
INNER JOIN `user_type`
ON (user.id=user_type.user_id);

Het resultaat hiervan is natuurlijk het volgende:

id	username	type_id
1	babbaloerie	1
1	babbaloerie	2
1	babbaloerie	3

Nu komt het probleem, een WHERE clause hier op uitvoeren heeft geen zin. type_id kan natuurlijk niet 1 en 2 tegelijk zijn. Ook moet ik unieke resultaten hier weergeven, maar een GROUP BY op id is hier illegaal en MySQL kiest dan gewoon een willekeurige rij uit en laat die zien als resultaat.

Ik gok dat ik dit op een heel andere manier moet doen en meer moet denken richting subquery's maar dat heeft voor mij alleen nog maar in errors geresulteerd.

Is er hier iemand die me hiermee kan helpen of een beetje de goede richting in kan sturen?

Bij voorbaat dank.

[sig69]

where type_id = 1 or type_id = 2?
where type_id in (1, 2)?

[ Voor 28% gewijzigd door sig69 op 25-02-2008 13:00 ]

[CodeCaster]

SQL:

HAVING type_id 1 OR type_id 2.

Da's dan 45 euro.

[ Voor 6% gewijzigd door CodeCaster op 25-02-2008 13:00 ]

[TheRookie]

Wat bedoel je precies: Wil je alle rijen waar type_id = 1 of 2 is, zoals bij de oplossingen hierboven

Of wil je alleen die users waarbij zowel type_id 1 als 2 aanwezig zijn ?

[Creepy]

CodeCaster schreef op maandag 25 februari 2008 @ 12:59:

SQL:

HAVING type_id 1 OR type_id 2.

Da's dan 45 euro.

Een having zonder een aggregated query?

Probeer dan een count op type_id, grouperen op de overige kolommen, een where met een type_id = 1 or type_id = 2 en vervolgens nog een having count(type_id) = 2

Edit: en ja, ik geef express geen uitgeschreven query

[ Voor 7% gewijzigd door Creepy op 25-02-2008 13:09 ]

[Voutloos]

Bovenstaande manier werkt prima, maar je zou ook gewoon 2x met die tabel joinen en voor de ene alias de ene check doen en voor de andere alias de andere. En zo zijn er nog wel een paar variaties mogelijk, welke geen van alle echt moeilijke queries zijn.

[Niemand_Anders]

Kleine optimalisatie: Beter verander je de where clausule naar een 'and' achter join (je beperkt daarmee de initiele resultaten welke de database moet samenvoegen (join). Een where wordt pas uitgevoerd nadat de tabellen zijn samengevoegd.

SQL:

SELECT user.id AS id, user.username AS username, user_type.type_id AS type_id
FROM `user` INNER JOIN `user_type` ON (user.id=user_type.user_id) and (user_type.type_id in (1,2));

[frickY]

Als je alleen users wilt met beide typen, zul je twee keer moeten joinen op de betreffende tabel

SQL:

SELECT 
   user.id AS id,
   user.username AS username,
   user_type.type_id AS type_id
FROM `user`
INNER JOIN `user_type` AS ut1 ON 
   ut1.user_id = user.id AND 
   ut1.type_id = 1
INNER JOIN `user_type` AS ut2 ON 
   ut2.user_id = user.id AND 
   ut2.type_id = 2

[ Voor 8% gewijzigd door frickY op 25-02-2008 13:45 ]

[leuk_he]

Ipv de group by/having kan hier ook een exists

select ....
from user
where exists (select 'X' from user_type where user_type.id = user.id and type_id in (1, 2))


of
...
where id in (select user_type.id from user_type where type_id in (1, 2))

Wellicht heeft imeand een goede link kant en klaar voor de topic starter? Zodat hij echt iets kan leren?

[Voutloos]

offtopic:
Wat wordt er weer gruwelijke voorgekauwd zeg. Mijn neefje van 8 kan ook mijn post en die van Niemand_Anders combineren en zo deze query verkrijgen. Creepy zegt expliciet al geen uitgeschreven query te willen geven en dan gebeurt dat alsnog... Heel veel regulars hier kunnen in 1x de oplossing opschrijven, dus dat is echt niet stoer. Het zo opschrijven dat ts weer aanknopingspunten heeft en een stuk verder kan komen en er echt van kan leren, dat is pas stoer.

[babbaloerie]

TheRookie schreef op maandag 25 februari 2008 @ 13:07:
Wat bedoel je precies: Wil je alle rijen waar type_id = 1 of 2 is, zoals bij de oplossingen hierboven

Of wil je alleen die users waarbij zowel type_id 1 als 2 aanwezig zijn ?

Dat dus, staat ook in de startpost
Is volgens mij niet helemaal begrepen door de meeste

[RobIII]

offtopic:
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

Het mag inderdaad wel wat minder voorgekauwd worden...

babbaloerie schreef op maandag 25 februari 2008 @ 14:40:
[...]

Dat dus, staat ook in de startpost
Is volgens mij niet helemaal begrepen door de meeste

...en je kunt er natuurlijk ook wat minder om vragen en eerst wat meer proberen

[ Voor 49% gewijzigd door RobIII op 25-02-2008 14:43 ]

[TheRookie]

@babbaloerie: De 2^e versie maakte ik ook uit je TS op, maar de eerste posters dachten dus iets anders, vandaar dat ik dacht: ik check 't ff

[babbaloerie]

De meeste dingen die in dit topic staan had ik voordat ik dit topic opende al geprobeerd. Ik had het er eigenlijk bij moeten vertellen, sorry hiervoor

Maar als je meerdere keren gaat joinen op dezelfde tabel, word de query dan niet veel langzamer na een tijdje?

[leuk_he]

babbaloerie schreef op maandag 25 februari 2008 @ 14:59:
Maar als je meerdere keren gaat joinen op dezelfde tabel, word de query dan niet veel langzamer na een tijdje?

Zolang het schaalbaar is: access via indexen, niet meerdere full table scans , en de zoektijd dus niet enorm toeneemt met het aantal rijen in de doeltabbellen maak je je daarover meestal niet zo druk.

[Niemand_Anders]

Een mogelijkheid is om een query te doen op user_type met daarop een group by en een having count welke controleert of het resultaat welke een user_id wordt terug gegeven gelijk is aan het aantal parameters van de 'in' clausule.

SQL:

select user_id from user_type 
   where type_id in (1,2) 
   group by user_id 
   having count(user_id) = 2;

Het resultaat hiervan kun je joinen of als subquery uitvoeren.


Hmm, voorkauwen is denk ik toch handiger dan de beschijving van de query. In elk geval leesbaarder ;-)

[Creepy]

offtopic:
Als je de beschrijving niet snapt of niet wilt snappen lijkt mij het verstandiger om gewoon maar te
stoppen met devven. Kant en klare oplossingen geven is leuk maar een hoop mensen nemen dan de oplossing domweg over zonder na te denken. Door het geven van een beschrijving dwing je ze min of meer om zelf alsnog wat na te denken zodat de kans groter is dat iemand er wat van leert. We zijn hier nog steeds tweakers en geen copy/paste coders...

Daarnaast laat je zelf al zien een aantal posts gewoon niet gelezen te hebben aangezien dat wat je voorstelt precies mijn beschrijving is die al behoorlijk is voorgekauwd..

En er zijn nu wel genoeg kant en klare oplossing gegeven dacht ik zo.... als er nog meer komen dan wordt het hier een opsom topic....

[ Voor 11% gewijzigd door Creepy op 25-02-2008 17:01 ]

[babbaloerie]

Ik heb nergens gezegd dat het voorgekauwd moet worden, lees de op één na laatste regel van de startpost maar...

[Creepy]

offtopic:
Het ging ook niet om jouw post maar om de posts van iedereen die een kant en klare oplossing heeft gegeven en de laatste post van Niemand_Anders in het bijzonder

[pedorus]

Nu lijkt het net alsof de having-oplossing 'beter' is en het lastiger is voor de DB-engine om dezelfde tabel 2x te bekijken. Volgens mij is die having-oplossing waarschijnlijk lastiger voor de DB-engine, lastiger op te splitsen in threads en minder makkelijk te begrijpen. Ik zou een oplossing als die van frickY dan ook aanraden.

Opm: Ik ga uit van indexen op type_id, user_id en user.id. Ik vraag me af waarom user_type een eigen id heeft. Is er ergens een verwijzende tabel met extra properties of kan een user meerdere keren hetzelfde type hebben ofzo? In het laatste geval gaat having niet goed werken.

offtopic:
Van spieken leer je meer dan van met een probleem blijven zitten

[Verwijderd]

Niemand_Anders schreef op maandag 25 februari 2008 @ 16:25:

SQL:

select user_id from user_type 
   where type_id in (1,2) 
   group by user_id 
   having count(user_id) = 2;

Zo krijg je alleen de user_id's die 2x voorkomen, niet die 1x type=1 en 1x type=2...
Tenzij er een constraint staat op unique user_id en type_id, dan heb ik niets gezegd.

[frickY]

Voutloos schreef op maandag 25 februari 2008 @ 13:50:

offtopic:
Wat wordt er weer gruwelijke voorgekauwd zeg. Mijn neefje van 8 kan ook mijn post en die van Niemand_Anders combineren en zo deze query verkrijgen. Creepy zegt expliciet al geen uitgeschreven query te willen geven en dan gebeurt dat alsnog... Heel veel regulars hier kunnen in 1x de oplossing opschrijven, dus dat is echt niet stoer. Het zo opschrijven dat ts weer aanknopingspunten heeft en een stuk verder kan komen en er echt van kan leren, dat is pas stoer.

Ik vind het nog veel jammerder dat de meeste het foute antwoord geven. Die komen meteen IN() terwijl de TS opzoek is naar gebruikers in groep 1 en 2.

Dus nogmaals; frickY in "\[PHP/MySQL] Selecteren uit relationele d..."

[ Voor 5% gewijzigd door frickY op 25-02-2008 22:46 ]

[Voutloos]

frickY schreef op maandag 25 februari 2008 @ 22:46:
Ik vind het nog veel jammerder dat de meeste het foute antwoord geven. Die komen meteen IN() terwijl de TS opzoek is naar gebruikers in groep 1 en 2.

Tja, dan moet je gewoon zeggen dat de antwoorden van sig69, CodeCaster en leuk_he fout zijn. De 1e query van Niemand_Anders is ook nog niet correct, maar is eenvoudig correct te maken als je de rest van de posts leest, waarmee je uit komt op jouw query.

Maar goed, aangezien het halve topic over beleid gaat: Net zo storend als de trend in /dev om oplossingen voor te kauwen is de trend om zo snel mogelijk zonder goed te lezen een foute query op te noemen.

pedorus schreef op maandag 25 februari 2008 @ 20:46:
Nu lijkt het net alsof de having-oplossing 'beter' is en het lastiger is voor de DB-engine om dezelfde tabel 2x te bekijken

Dat is 'De Grote Angst Voor Joins', welke totaal ongegrond is. Twee joins met keys is peanuts.

offtopic:
Van spieken leer je meer dan van met een probleem blijven zitten

Met een probleem blijven zitten gebeurt nooit, want je wordt hier altijd geholpen.

[leuk_he]

Voutloos schreef op dinsdag 26 februari 2008 @ 08:09:
[...]
Tja, dan moet je gewoon zeggen dat de antwoorden van sig69, CodeCaster en leuk_he fout zijn.

lezen is zo moeilijk voor de meeste hier. Feedback als "heb ik ook geprobeerd" helpt niet, omdat je dan niet duidelijk maakt wat er dan fout gaat.