[mysql] veemd opgebouwde tabel doorzoeken

[RSD]

Ik heb een voor mij vreemde tabel die er alsvolgt uitziet:

naam property value
jan ogen blauw
jan bouw mager
jan geslacht man
pietje ogen grijs
pietje bouw slank
pietje geslacht man
miep ogen groen
miep bouw vet
miep geslacht vrouw

Hierop moet ik een zoekfunctie maken dat als je bij geslacht man selecteert dat hij dan alle mannen eruit haalt. Als je nu bij de ogen blauw selecteert moet hij de mannen met blauwe ogen selecteren.

Ik heb echt al van alles geprobeerd. Zelf zou ik een tabel nooit zo opbouwen, ik zou dan bij de user de velden "ogen,bouw,geslacht" maken, maar ik kan dit niet aanpassen, ik heb hier geen invloed op. De tabel wordt zoals hierboven aangeleverd. In eerste instantie dacht ik dat doe ik wel even, maar het lijkt ingewikkelder dan dt ik dacht, of ik moet echt iets gruwelijks over het hoofd zien.

Ik heb al van alles geprobeerd. Ik begon met bijvoorbeeld:

WHERE geslacht='mand' AND ogen='blauw', dit gaat niet werken, omdat de WHERE per rij bekeken wordt. Een OR zou wel kunnen, maar dan toont hij alle mannen en alle blauwe ogen, dus ook vrouwen met blauwe ogen en dat is niet de bedoeling.

Nu zit ik het volgende te proberen:

SQL:

SELECT
 naam,
 IF(property='geboren',value,NULL) AS age,
 IF(property='geslacht',value,NULL) AS gender,
 IF(property='taal',value,NULL) AS taal,
 IF(property='ogen',value,NULL) AS ogen,
 IF(property='sterrenbeeld',value,NULL) AS sterrenbeeld,
 IF(property='lichaamsbouw',value,NULL) AS lichaamsbouw,
 IF(property='haar',value,NULL) AS haar
FROM profielen

Dit is ook niks zover als ik nu ben. Met CASE heb ik ook al een en ander geprobeerd, maar ook zonder succes. Het is de vreemde manier van een tabel opbouwen voor mij dat het niet lukt.

Heeft iemand een suggestie?

[HTeK]

De SQL oplossing hiervoor zou ik zo ook even niet kunnen bedenken Maar je zou het eventueel met meerdere queries kunnen oplossen, dus eerst de mannen er uit halen en dan met een nieuwe query de de mannen met blauwe ogen eruit halen.

[RSD]

Het moet wel een beetje vlot blijven gaan ;-)

[ibmos2warp]

Ik ben niet zo'n kei in sql, maar is het niet gewoon voor alle mannen:

SQL:

SELECT naam FROM profielen WHERE property='geslacht' AND value='man'

?
En als je dan op ogenkleur dat je dan geslacht en man veranderd in die mogelijkheden?
En als blauwe ogen er bij komt is het zo:

SQL:

SELECT naam FROM profielen WHERE property='geslacht' AND value='man' AND property='ogenkleur' AND value='blauw'

Edit: Moet ik het dus wel goed doen, en naar mijn id zou het zo moeten werken. Anders moet het misschien zoals hieronder aangeeft met subquery.

[ Voor 56% gewijzigd door ibmos2warp op 29-11-2007 18:00 ]

[masq]

Subquery gebruiken?

bijv:

SQL:

SELECT *
FROM profielen
WHERE naam IN (
  SELECT naam
  FROM profielen
  WHERE property='geslacht' AND value='man')
AND property='ogen' AND value='blauw';

[RuudvandeBeeten]

ibmos2warp schreef op donderdag 29 november 2007 @ 17:48:
Ik ben niet zo'n kei in sql, maar is het niet gewoon voor alle mannen: select naam from tabel where geslacht='man' ?
En als je dan op ogenkleur dat je dan geslacht en man veranderd in die mogelijkheden?
En als blauwe ogen er bij komt is het zo:
select naam from tabel where geslacht='man' and ogenkleur='blauw'

Wat hij zegt.

select * from tabel where geslacht = man AND ogenkleur = roodmetwittestippen

mocht je in plaats van ogenkleur iets anders uit de tabel pak je een andere querie

[RSD]

En wat als ik 6 of 7 van die mogelijkheden heb?

[Verwijderd]

RSD schreef op donderdag 29 november 2007 @ 17:55:
En wat als ik 6 of 7 van die mogelijkheden heb?

Dan 5 of 6 subqueries. Dat is nu eenmaal het nadeel van een dergelijke structuur.

[RSD]

RuudvandeBeeten schreef op donderdag 29 november 2007 @ 17:54:
[...]

Wat hij zegt.

select * from tabel where geslacht = man AND ogenkleur = roodmetwittestippen

mocht je in plaats van ogenkleur iets anders uit de tabel pak je een andere querie

Nee dat werkt niet door de opbouw van de tabel. geslacht is geen veld maar een record.

[RSD]

Maar moeten die subqueries dan in elkaar zitten of kan ik gewoon doen

AND naam IN (subquery) AND naam IN (anderesubquery)

[Verwijderd]

Probeer het.

[RSD]

Gelukt, maar het kan dus niet makkelijker?

[pimlie]

Een andere optie is wellicht een query als dit:

SQL:

SELECT Naam,
GROUP_CONCAT(Value ORDER BY Property SEPARATOR ',') AS Id
FROM Tabel
WHERE Property IN ('geslacht','ogen')
GROUP BY Naam
HAVING Id = 'man,blauw'

Geen idee hoe dit performt t.o. subqueries, maar het werkt wellicht iets overzichtelijker

[ibmos2warp]

RSD schreef op donderdag 29 november 2007 @ 17:58:
[...]

Nee dat werkt niet door de opbouw van de tabel. geslacht is geen veld maar een record.

En dat heb ik ook verandert. Maar dat van mij zal wel niet werken idd.

[Creepy]

RSD schreef op donderdag 29 november 2007 @ 18:23:
Gelukt, maar het kan dus niet makkelijker?

Tuurlijk wel, maar dan zou je je datastructuur moeten omgooien naar iets dat genormaliseerd is i.p.v. een tabel waar je alles maar in gooit

[Kalentum]

Een mooie flexibele datastructuur!

Je kan die ene tabel zien als een reeks tabellen: eentje voor elke property die je hebt. Dus je hebt een tabel 'ogen' en een tabel 'geslacht'. Elke tabel heeft als "primaire sleutel" de naam.

Door nu voor elke property een join te doen op zich zelf kun je gegevens aan elkaar koppelen.

Om alle mannen met blauwe ogen te krijgen wordt het iets als

SQL:

SELECT DISTINCT n.naam FROM properties n
INNER JOIN properties geslacht ON (geslacht.naam = n.naam AND geslacht.property='geslacht')
INNER JOIN properties ogen ON (ogen.naam = n.naam AND ogen.property = 'ogen')
WHERE ogen.value = 'blauw' AND geslacht.value='man'

[ Voor 0% gewijzigd door Kalentum op 29-11-2007 19:42 . Reden: SQL spreekt geen Nederlands ]

[Verwijderd]

RSD schreef op donderdag 29 november 2007 @ 18:23:
Gelukt, maar het kan dus niet makkelijker?

Als je het database model inderdaad niet aan kan passen heb je maar 2 opties: subqueries of groeperen. Je zou eventueel een test uit kunnen voeren welke van deze twee querys het snelste een resultaat oplevert, door beide 10, 100 of 1000 (kies maar) maal uit te voeren en dan de gemiddelde executietijd van je script/query uit te laten rekenen.