[MySQL/SQL] Selectie uit koppeltabel op meerdere specifiek *

[Massiefje]

Ik zit met een irritant probleem, waarvan de een zegt: je kan het met subqueries oplossen en de ander zegt: je database model is niet correct.

Het gaat om het volgende: Ik heb 3 tabellen. 1 tabel (bedrijven), met alle bedrijfsgegevens + bedrijf_id, 1 tabel (categerorien) met categorien en een cat_id en 1 tabel (bedrijf_cat) met de koppeling tussen bedrijf_id en cat_id, zodat ik makkelijk kan uitzoeken welke bedrijf in welke categorie zit.

In principe doet dit precies wat het moet doen, maar nu het volgende.

Ik zou graag alle bedrijf_id's willen, waarin zowel cat_id 1 EN cat_id 2 voorkomt.

Als ik dit met een innerjoin en een where clausule doe, en ik gebruik hierbij AND, dan krijg ik GEEN resultaten. doe ik hetzelfde met de where functie en OR, dan krijg ik bedrijven die OF 1 OF 2 als cat_id hebben.

Het moet toch mogelijk zijn met dit databasemodel om dit goed te krijgen, gebruik makend van een sinqle (sub)query ? ?

Wie heeft het antwoord ?

[ Voor 3% gewijzigd door Massiefje op 24-09-2004 09:36 ]

[justmental]

Dat kan zonder subquery's.
Twee inner joins vanuit bedrijven naar bedrijf_cat is genoeg.
dus 2x bedrijf_cat opnemen met (uiteraard) een verschillend alias.

Als je de vraag verandert in 'de bedrijven die in alle categorieen zitten' dan wordt het een stuk lastiger.

[ Voor 19% gewijzigd door justmental op 24-09-2004 09:39 ]

[Massiefje]

justmental schreef op 24 september 2004 @ 09:38:
Dat kan zonder subquery's.
Twee inner joins vanuit bedrijven naar bedrijf_cat is genoeg.

Als je de vraag verandert in 'de bedrijven die in alle categorieen zitten' dan wordt het een stuk lastiger.

Euhm, hoe bedoel je dit precies: 2 inner joins ? Zou je eventueel een voorbeeld kunnen geven ?

[Verwijderd]

Je model lijkt me OK, een n-op-m-relatie tussen bedrijf en categorie. Als je twee categorieen wilt selecteren en er is geen verband tussen deze categorieen lijkt me een andere indeling van gegevens niet zinvol.

Het probleem kan je met 1 query oplossen met UNION :
query catA
UNION
query catB

Met UNION of UNION ALL kan je verder nog onderscheid maken of je de bedrijven mogelijk dubbel vermeld wilt zien als ze in meer dan 1 geselecteerde categorie vallen.

Edit: dit vroeg je niet, ik kan niet lezen .

[ Voor 6% gewijzigd door Verwijderd op 24-09-2004 09:44 ]

[whoami]

Massiefje schreef op 24 september 2004 @ 09:39:
[...]


Euhm, hoe bedoel je dit precies: 2 inner joins ? Zou je eventueel een voorbeeld kunnen geven ?

Met een alias:

code:

select *
from bedrijf
INNER JOIN bedrijf_cat ON ....
INNER JOIN bedrijf_cat bedrijf_cat2 ON ....
WHERE bedrijf_Cat.categorie_id = 1
AND bedrijf_cat2.categorie_id = 2

[justmental]

Massiefje schreef op 24 september 2004 @ 09:39:
Euhm, hoe bedoel je dit precies: 2 inner joins ? Zou je eventueel een voorbeeld kunnen geven ?

code:

select bla
from tabel1 as b1
, tabel2 as a1
, tabel3 as a2
where a1.id=1
and a2.id=2
and a1.join_kolom = b1.join_kolom
and a2.join_kolom = b1.join_kolom

[Verwijderd]

Poging 2: Inner join, tel het aantal records, dat moet 2 zijn:

code:

SELECT 
    Bedrijf.ID, COUNT(*)
FROM 
    Bedrijf, BedrijfCcategorie
WHERE
    Bedrijf.ID=BedrijfCategorie.BID AND 
    (BedrijfCategorie.CID=x OR BedrijfCategorie.CID=y)
GROUP BY 
    Bedrijf.ID
HAVING 
    COUNT(*)=2

[justmental]

Verwijderd schreef op 24 september 2004 @ 09:49:
Poging 2: Inner join, tel het aantal records, dat moet 2 zijn:

code:

SELECT 
    Bedrijf.ID, COUNT(*)
FROM 
    Bedrijf, BedrijfCcategorie
WHERE
    Bedrijf.ID=BedrijfCategorie.BID AND 
    (BedrijfCategorie.CID=x OR BedrijfCategorie.CID=y)
GROUP BY 
    Bedrijf.ID
HAVING 
    COUNT(*)=2

Dat vroeg ie ook niet
Hij vroeg cat 1 en 2, niet 2 categorieen.

[Massiefje]

Wat er misschien nog even bij vermeld "moet" worden: het gaat om een dynamische website, waarbij de categorieen uitgebreid kunnen gaan worden in de toekomst. Klanten kunnen inloggen op de website en eventueel meerdere categorieen aangeven, met andere woorden: geef me alle bedrijven waar ik een boot kan huren (cat_id: 1) en alle bedrijven waar ik een drankje kan halen (cat_id: 2), maar ook kan eten (cat_id: 3). Als voorbeeld uiteraard.

[Verwijderd]

justmental schreef op 24 september 2004 @ 09:50:
[...]

Dat vroeg ie ook niet
Hij vroeg cat 1 en 2, niet 2 categorieen.

Deze categorieen staan in mijn code:

code:

(BedrijfCategorie.CID=x OR BedrijfCategorie.CID=y)

Gelukkig word ik omringd door lotgenoten .

[justmental]

Massiefje schreef op 24 september 2004 @ 09:51:
Wat er misschien nog even bij vermeld "moet" worden: het gaat om een dynamische website, waarbij de categorieen uitgebreid kunnen gaan worden in de toekomst. Klanten kunnen inloggen op de website en eventueel meerdere categorieen aangeven, met andere woorden: geef me alle bedrijven waar ik een boot kan huren (cat_id: 1) en alle bedrijven waar ik een drankje kan halen (cat_id: 2), maar ook kan eten (cat_id: 3). Als voorbeeld uiteraard.

Dan maak je er een dynamische query van.

Verwijderd schreef op 24 september 2004 @ 09:52:
Deze categorieen staan in mijn code:

code:

(BedrijfCategorie.CID=x OR BedrijfCategorie.CID=y)

Gelukkig word ik omringd door lotgenoten .

ah, over het hoofd gezien

[ Voor 21% gewijzigd door justmental op 24-09-2004 09:54 ]

[Verwijderd]

Het kan zonder subquery

code:

select distinct b*
from bedrijf b
inner join koppeltabel k
on b.b_id = k.b_id
where k.type_id in (1,2,3)

maar een subquery zal sneller zijn als het goed is:

code:

select *
from bedrijf b
where exists 
( 
  select * 
  from koppel k
  where k.b_id = b.b_id 
  and k.type_id in (1,2,3)
)

[ Voor 3% gewijzigd door Verwijderd op 24-09-2004 09:56 . Reden: layout ]

[justmental]

Verwijderd schreef op 24 september 2004 @ 09:54:
Het kan zonder subquery

code:

select distinct b*
from bedrijf b
inner join koppeltabel k
on b.b_id = k.b_id
where k.type_id in (1,2,3)

maar een subquery zal sneller zijn als het goed is:

code:

select *
from bedrijf b
where exists ( select * from koppel k where k.b_id = b.b_id and k.type_id in (1,2,3)

Dat is 'waar bedrijf in minimaal 1 categorie valt'.

[gorgi_19]

En titelchange gedaan; mensen met een betere suggestie kunnen deze kenbaar maken via Topic Report

[Prutser_IA]

Je datamodel is gewoon goed, er vanuitgaande dat een bedrijf in meerdere catergorien kan zitten (dus n-n relatie) en daar een net koppeltabelletje tussen. Wat wel gebruikelijk is, is om de tabel namen enkelvoudig te houden. Dus bedrijf ipv bedrijven. Dit omdat elke record 1 bedrijf is, en geen n bedrijven.

Weet niet precies met welk database systeem je werkt, omdat mysql toch zo'n zijn beperkingen heeft...

Volgens mij zou zoiets goed kunnen werken (uit de losse pols). De cat1 list zou je in een variabele kunnen zetten zodat je systeem inderdaad uit te breiden valt.

code:

SELECT  «wat je weten wilt»
FROM    bedrijven 
WHERE   bedrijf_id in (
  SELECT bedrijf_id 
  FROM   bedrijf_cat 
  WHERE  cat_id in (
    SELECT cat_id 
    FROM   categorien 
    WHERE  LABEL in ('cat1', 'cat2')
  )
)

Met php is het wellicht beter om 2 queries te maken

PS. een wildcard * is niet heel netjes om te gebruiken, probeer de colummen gewoon in je select op te geven.

[ Voor 10% gewijzigd door Prutser_IA op 24-09-2004 10:06 ]

[Verwijderd]

Had de AND even over het hoofd gezien.

Een aantal alternatieven:

code:

select *
from bedrijf b
where exists 
( 
  select 1 
  from koppel k
  where k.b_id = b.b_id 
  and k.type_id = 1
)
AND exists
( 
  select 1 
  from koppel k
  where k.b_id = b.b_id 
  and k.type_id = 2
)
AND exists
( 
  select 1 
  from koppel k
  where k.b_id = b.b_id 
  and k.type_id = 3
)
-- etc..

of zo

code:

select b.*
from bedrijven b
inner join koppel k1 on k1.b_id = b.b_id and k1.type_id=1
inner join koppel k2 on k2.b_id = b.b_id and k2.type_id=2
inner join koppel k3 on k3.b_id = b.b_id and k3.type_id=3

vergezocht:

code:

select *
from bedrijven b
where ( 
  select count( distinct type_id )
  from koppel k
  where k.b_id = b.b_id
  and k.type_id in ( 1,2,3)
) = 3 -- het aantal types dat je verwacht

[ Voor 2% gewijzigd door Verwijderd op 24-09-2004 10:07 . Reden: foutjes ]

[whoami]

TheCKU: een (inner) join is in vrijwel alle gevallen sneller dan een subquery.

[curry684]

whoami schreef op 24 september 2004 @ 10:13:
TheCKU: een (inner) join is in vrijwel alle gevallen sneller dan een subquery.

Zeg maar 'alle', omdat een subquery in het beste geval tot de equivalente join weggeoptimaliseerd kan worden en een join met correct gestelde index een hash-match aangaat, wat de snelste operatie is die een database kan doen over meerdere tabellen

[justmental]

curry684 schreef op 24 september 2004 @ 10:36:
Zeg maar 'alle', omdat een subquery in het beste geval tot de equivalente join weggeoptimaliseerd kan worden en een join met correct gestelde index een hash-match aangaat, wat de snelste operatie is die een database kan doen over meerdere tabellen

Als je een exist hebt met een subquery die meerdere rijen op kan leveren dan is deze in Oracle sneller dan een join variant.

[Verwijderd]

Dan geef ik toch de voorkeur aan mijn "Poging 3" hieronder, zonder joins en subselects maar waar de WHERE en HAVING clausule variabel zijn.

Als extraatje kan met deze aanpak de telling in HAVING losser gelaten worden, waardoor queries mogelijk zijn als: selecteer bedrijven die voorkomen in tenminste 3 van de gezochte 5 categorieen .

Edit: Poging 3: nog simpeler, geen joins meer:

code:

SELECT 
    BID, COUNT(*)
FROM 
    BedrijfCategorie
WHERE
    (BedrijfCategorie.CID=x OR BedrijfCategorie.CID=y)
GROUP BY 
    BID
HAVING 
    COUNT(*)=2

[ Voor 32% gewijzigd door Verwijderd op 24-09-2004 12:23 ]

[Verwijderd]

@whomai en curry684: Een subquery hoeft zeker niet perse meer te kosten als een inner join. Met een EXISTS kan na de eerste instantie al worden gestopt met zoeken, terwijl een INNER JOIN alle mogelijke joins moet ophoesten. Hangt dus van het geval af.