[(my)sql] rekenen met subselects?

[Chrotenise]

Hoi medetweakers,

Op het moment zijn wij bij mijn cursus aangekomen bij het gebruiken van subselects. De opgaven die ik moest doen, moeten dus ook opgelost worden met alleen (sub)selects. Geen joins/unions e.d.

Ik dus overijverig aan het werk en alles is in principe gelukt, behalve deze laatste hersenkraker. Ik heb google afgeplozen uiteraard samen met de handige instructietopic van dit forum, maar heb niet het uiteindelijke antwoord gevonden. Ik hoop nu dat jullie tot mijn redding kunnen komen.

De opgave is als volgt:

'geef het soortnummer(snr) en de nederlandse soortnaam(nsnaam) van die soorten die uitsluitend vis als voedsel hebben.'

Koppeling is als volgt:

snr/snaam zijn columns van soort
snr/voednr zijn columns van dieet
voedsoort/voednr zijn columns van voedsel

Mijn query:

SELECT IFNULL(snr,0) , snaam) FROM soort WHERE snr IN -- om eventule nulwaardes eruit te filteren, ifnull gedaan.
( SELECT snr FROM dieet WHERE voednr IN
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' AND snr = -- hierna gaat het dus fout
( SELECT count(snr) = 1 FROM dieet)));

Ik ben tot het volgende gekomen met de volgende logica:

Eindresultaat moet van soort zijn;
Soort moet je koppelen aan dieet om bij voedsel te komen;
In dieet kan je kijken hoeveel verschillende soorten voedsel elke soort eet. Hier dien je dus te filteren naar dat het soort maar 1 soort voedsel eet (vis.)
In voedsel dien je uiteindelijk het resultaat te filteren naar vis.
Omdat dieet eerder komt dan voedsel, heb ik nog een laatste subselect gedaan terug naar dieet omdat je eerst moet filteren naar aantal verschillende soorten voedsel voordat je de 'vis' selectie kan doen, anders klopt de output niet.

Ik moet de count in de laatstse subselect gelijkstellen aan 1, omdat het maar een soort voedsel mag eten. Deze moet vervolgens doorgegeven worden aan de volgende subselect, alleen dat gebeurt niet. Ik krijg namelijk helemaal geen output meer. Als ik die laatste subselect weglaat, dan krijg ik er 7. Moeten er 3 zijn.

[ Voor 4% gewijzigd door Chrotenise op 09-12-2012 00:58 ]

[MrAngry]

Heb je al geprobeerd om die count in de where clause van je eerste subquery te trekken:
SELECT snr FROM dieet WHERE voednr IN
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' ) AND count(snr) = 1

[Chrotenise]

MrAngry schreef op zondag 09 december 2012 @ 01:01:
Heb je al geprobeerd om die count in de where clause van je eerste subquery te trekken:
SELECT snr FROM dieet WHERE voednr IN
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' ) AND count(snr) = 1

Krijg ik error 1111: invalid use of group function. Daarnaast is snr geen attribuut van voedsel, alleen van dieet.

[ Voor 7% gewijzigd door Chrotenise op 09-12-2012 01:06 ]

[MrAngry]

Ok, nou er is natuurlijk een andere manier dan count() om te selecteren voor alleen vis (de foutmelding komt doordat je hier having moet gebruiken, maar ik heb hier geen SQL dus weet niet of dat samengaat met je subquery). Die is wel lelijk, maar wellicht is het in dit geval de bedoeling, iets van not in ('vlees', 'planten') etc. Of <>

[Chrotenise]

MrAngry schreef op zondag 09 december 2012 @ 01:14:
Ok, nou er is natuurlijk een andere manier dan count() om te selecteren voor alleen vis (de foutmelding komt doordat je hier having moet gebruiken, maar ik heb hier geen SQL dus weet niet of dat samengaat met je subquery). Die is wel lelijk, maar wellicht is het in dit geval de bedoeling, iets van not in ('vlees', 'planten') etc. Of <>

Het probleem is niet zozeer dat ik niet kan filteren op de waardes van voedselsoort om vis eruit te krijgen. Het probleem is dat je die uitkomsten moet koppelen aan snr om te kijken of die diersoort ook nog andere dingen eet. Ik mag namelijk uiteindelijk alleen de diersoorten weergeven die alleen vis eten.

[Kalief]

voedsoort/voednr zijn columns van voedsel
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' AND snr = -- hierna gaat het dus fout

voedsoort = voedselsoort ?

[Chrotenise]

Kalief schreef op zondag 09 december 2012 @ 01:57:
voedsoort/voednr zijn columns van voedsel
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' AND snr = -- hierna gaat het dus fout

voedsoort = voedselsoort ?

Ja.

[Rotterdammertje]

Chrotenise schreef op zondag 09 december 2012 @ 00:56:
Mijn query:

SELECT IFNULL(snr,0) , snaam) FROM soort WHERE snr IN -- om eventule nulwaardes eruit te filteren, ifnull gedaan.
( SELECT snr FROM dieet WHERE voednr IN
( SELECT voednr FROM voedsel WHERE voedselsoort = 'vis' AND snr = -- hierna gaat het dus fout
( SELECT count(snr) = 1 FROM dieet)));

Ik denk dat je hier al een aardig eind op weg mee bent. Misschien moet je het eens proberen zonder die count, maar moet je kijken naar alle dieren die een dieet hebben waar de voedselsoort bestaat uit vis, EN waarbij de voedselsoort niet bestaat uit iets anders dan vis (hint hint).

[Chrotenise]

Rotterdammertje schreef op maandag 10 december 2012 @ 09:43:
[...]


Ik denk dat je hier al een aardig eind op weg mee bent. Misschien moet je het eens proberen zonder die count, maar moet je kijken naar alle dieren die een dieet hebben waar de voedselsoort bestaat uit vis, EN waarbij de voedselsoort niet bestaat uit iets anders dan vis (hint hint).

Dat had ik al door, maar mijn vraag is hoe je dat doet. Want dat komt er op neer dat je 'mag niet anders dan niet vis zijn.' Dit heb ik nog nooit hoeven neerzetten en het enige wat ik kon bedenken werkt niet, nl:

AND NOT voedselsoort IS NOT 'vis'
AND voedselsoort NOT LIKE <> 'vis'

En dat leek me ook niet hoe ik het moest neerzetten. Ik heb vervolgens het geprobeerd door twee subselects in dieet op te nemen:

code:

SELECT IFNULL(snr,0), nsnaam -- 10 stuck @ de andere voedselsoorten eruit filteren.
FROM soort
WHERE snr IN (  SELECT snr
                FROM dieet
                WHERE voednr IN (   SELECT voednr
                                    FROM voedsel
                                    WHERE voedselsoort = 'vis')
                AND voednr NOT IN (     SELECT voednr 
                                    FROM voedsel
                                    WHERE voedselsoort NOT LIKE '%vis%'));

Dit zorgt voor de oude bekende met 7 output i.p.v. 3

[ Voor 36% gewijzigd door Chrotenise op 11-12-2012 01:11 ]

[pedorus]

Dat NOT LIKE enzo lijkt me onzin, voedselsoort<>'vis' zou goed genoeg zijn. Het probleem is dat je de niet-constructie te laat begint. Een dieet is nu goed als er een voedselsoort in zit dat goed bevonden is. Afhankelijk van of een soort meer regels in de tabel dieet heeft, moet je de niet-constructie al gelijk beginnen (met meerdere subselects). Dus je zoekt soorten die geen dieet hebben waar foute dingen opstaan, en wel een dieet hebben met vis erin.

Kijk trouwens ook eens naar (NOT) EXISTS, ik vermoed dat de opdracht op het gebruikt daarvan doelt.

[Chrotenise]

pedorus schreef op dinsdag 11 december 2012 @ 01:27:
Dat NOT LIKE enzo lijkt me onzin, voedselsoort<>'vis' zou goed genoeg zijn. Het probleem is dat je de niet-constructie te laat begint. Een dieet is nu goed als er een voedselsoort in zit dat goed bevonden is. Afhankelijk van of een soort meer regels in de tabel dieet heeft, moet je de niet-constructie al gelijk beginnen (met meerdere subselects). Dus je zoekt soorten die geen dieet hebben waar foute dingen opstaan, en wel een dieet hebben met vis erin.

Kijk trouwens ook eens naar (NOT) EXISTS, ik vermoed dat de opdracht op het gebruikt daarvan doelt.

<> mag blijkbaar niet in in die combinatie; mysql keurde het niet goed.

Ik heb vanwege wat jij zei inderdaad nu twee subselects aan dieet gehangen (zie vorige post): eentje geeft een lijst terug met welke voednrs toegestaan zijn en eentje welke niet toegestaan is. Probleem is dat hij de laatste lijst niet gebruikt om de eerste lijst te filteren.

En nee, dit moet echt met alleen subselects icm wheres op te lossen zijn volgens de docent.

[Chrotenise]

Eureka!

code:

SELECT IFNULL(snr,0) nsnaam -- 10 stuck @ de andere voedselsoorten eruit filteren.
FROM soort
WHERE snr IN (  SELECT snr
                FROM dieet
                WHERE snr NOT IN   ( SELECT DISTINCT snr
                                        FROM dieet
                                        WHERE voednr IN (   SELECT voednr
                                                            FROM voedsel
                                                            WHERE voedselsoort <> 'vis' ))
                AND snr IN (    SELECT snr
                                FROM dieet
                                WHERE voednr IN (   SELECT voednr
                                                    FROM voedsel
                                                    WHERE voedselsoort = 'vis' )));

[Rotterdammertje]

Chrotenise schreef op dinsdag 11 december 2012 @ 02:09:
Eureka!

code:

SELECT IFNULL(snr,0) nsnaam -- 10 stuck @ de andere voedselsoorten eruit filteren.
FROM soort
WHERE snr IN (  SELECT snr
                FROM dieet
                WHERE snr NOT IN   ( SELECT DISTINCT snr
                                        FROM dieet
                                        WHERE voednr IN (   SELECT voednr
                                                            FROM voedsel
                                                            WHERE voedselsoort <> 'vis' ))
                AND snr IN (    SELECT snr
                                FROM dieet
                                WHERE voednr IN (   SELECT voednr
                                                    FROM voedsel
                                                    WHERE voedselsoort = 'vis' )));

Het kan trouwens nog iets korter. Je doet nu een query op dieet met daarin weer een query op dieet. Een beetje overvloedig; die eerste "SELECT snr FROM dieet" hoeft volgens mij niet.

[ Voor 12% gewijzigd door Rotterdammertje op 11-12-2012 13:30 ]

[Chrotenise]

Rotterdammertje schreef op dinsdag 11 december 2012 @ 12:45:
[...]




Het kan trouwens nog iets korter. Je doet nu een query op dieet met daarin weer een query op dieet. Een beetje overvloedig; die eerste "SELECT snr FROM dieet" hoeft volgens mij niet.

Goed gezien. Bleek ook dat *** MySQL een null waarde liet zien zodat je makkelijker dingen kan toevoegen. Dacht dat mijn SQL query verkeerd was . IFNULL() hoefde dus ook niet.

[pedorus]

Let overigens op dat een NOT IN-constructie andere resultaten oplevert dan een NOT EXISTS-constructie als er wel een NULL in de set zit. Uitleg: http://www.techrepublic.c...ect-in-and-exists/5319615

Overigens is die DISTINCT ook overbodig