[SQL] combineren 2 queries

[Verwijderd]

Voor mijn afstudeerproject heb ik oa. Een kleine database gebouwd om wat overzicht over mijn data te houden.
Ik ben nu echter met een query bezig waar ik niet helemaal uitkom.

Bij deze query gaat het om 1 tabel met de kolommen: cellid, vegcode en coverage.

1 Cellid kan meerdere vegcode bevatten die elk een eigen coverage hebben.

Nu zou ik graag het aantal cellen willen weten waar “sg” de maximum coverage is maar waarbij de coverage “bs” niet meetelt.
(“sg” staat voor short grass, “bs” voor bare soil, en die laatste is niet relevant als coverage in mijn geval).

De (een) query voor het aantal cellen waar “sg” de maximum coverage heeft (grootste coverage in die cel) is:

code:

SELECT count(vegetation.cellid) 
FROM vegetation 
WHERE (((vegetation.coverage) In (select max (vegetation.coverage) FROM vegetation 
GROUP BY vegetation.cellid)) 
AND ((vegetation.vegcode)="sg"));

De (een) werkende query om de cellid’s, vegcodes en coverages te selecteren zonder “bs” is:

code:

SELECT vegetation.cellid, vegetation.coverage, vegetation.vegcode 
FROM vegetation
WHERE vegetation.vegcode <> "bs"
GROUP BY vegetation.cellid, vegetation.coverage, vegetation.vegcode

Deze 2 queries zou ik dus moeten combineren. Maar ik kom er niet uit.
In eerste instantie dacht ik om de 2e query als subquery in de 1ste in te bouwen maar dat werkt niet. Ook door de 2e query als subquery in het FROM statement van de 1ste query in te bouwen ging niet, of lukte me niet (ben nog redelijk newbie in SQL).

Overigens werk ik in MS Access 4.1 dus subqueries werken wel.

[blizt]

Ik neem aan dat je P&W FAQ - SQL al gelezen hebt?

offtopic:
Ik heb je post globaal doorgelezen, maar volgens mij is dit wat je zoekt.

[Creepy]

Verwijderd schreef op donderdag 16 december 2004 @ 13:09:
Overigens werk ik in MS Access 4.1 dus subqueries werken wel.

Eeeh. Bedoel je MySQL 4.1 waarin subqueries wel werkten (en daarvoor niet). Of bedoel je dat je gewoon in Access werkt waarin subqueries al een hele poos wel werken?

Melden als "het werkt niet" hebben we ook niks aan. Geef dan aan wat je precies hebt gedaan en wat daar niet mee lukte. Zie daarnaast de opmerking van blitzt

[ Voor 14% gewijzigd door Creepy op 16-12-2004 18:25 ]

[Verwijderd]

Ik werk idd gewoon in access.
De FAQ heb ik gelezen. Ik heb inmiddels al heel wat geprobeerd, begin een beetje te vervallen in weinig productief trial and error. Kan hier wel alles neergooien wat ik geprobeerd heb maar dat lijkt me weinig verhelderend.

Het probleem is om de 2e query als subquery in de eerste in te bouwen. Tenminste, dat lijkt me juist. Immers, de subquery wordt eerst uitgevoerd, dus dan worden alle vegetaties (+ bijbehorende cellid en coverage) gesecteerd op "bs" na. Vervolgens worden dan de max coverage per cellid geselecteerd. Of zit ik hier al verkeerd?

Wat ik oa. geprobeerd heb is het volgende:

code:

SELECT (vegetation.cellid)
FROM vegetation
WHERE EXISTS (select vegetation.cellid, vegetation.coverage, vegetation.vegcode FROM vegetation WHERE vegetation.vegcode <> "bs") AND (((vegetation.coverage) IN (select max (vegetation.coverage) 
FROM vegetation
GROUP BY vegetation.cellid))
AND vegetation.vegcode = "sg")

Deze query geeft geen foutmelding, maar het geeft niet het goede resultaat. Hij neemt nl ook "bs" mee bij het selecteren van de max coverages...

Dus mijn vragen zijn: Is mijn gedachtengang goed? Zo nee, waar ga ik de mist in?

Is mijn gedachtengang goed, hoe krijg ik de 2e query dan goed als subquery in mijn 1ste query? (Of wat doe ik fout bij mijn bovenstaande laatste poging?). Ik hoop dat eea nu iets duidelijker is.

[ Voor 4% gewijzigd door Verwijderd op 20-12-2004 11:47 ]

[Jaspertje]

K vun het lastig omdat ik geen faluw idee heb wat er precies moet gebeuren, maar die query van hierboven zou volgends mijn ook zo geschreven kunnen worden

code:

SELECT  vegetation.cellid, vegetation.coverage 
FROM vegetation
WHERE vegetation.cellid In (select vegetation.cellid FROM vegetation WHERE vegetation.vegcode <> "bs")
and vegetation.coverage IN (select max (vegetation.coverage) FROM vegetation WHERE vegetation.vegcode <> "bs")

[ Voor 12% gewijzigd door Jaspertje op 20-12-2004 11:53 ]

[Verwijderd]

Helaas,

Geen foutmelding, ander resultaat, maar niet het goede

Hieronder de hele vegetation tabel (tenminste, de oefentabel, echte tabel heeft ongeveer 30 000 records):



Query:

code:

SELECT (vegetation.cellid), (vegetation.coverage), (vegetation.vegcode)
FROM vegetation
WHERE EXISTS (select vegetation.cellid, vegetation.coverage, vegetation.vegcode FROM vegetation WHERE vegetation.vegcode <> "bs") AND vegetation.coverage IN (select max (vegetation.coverage) 
FROM vegetation
GROUP BY vegetation.cellid
AND vegetation.vegcode = "sg")

Resultaat:
E7-2 bs 80
E7-4 sg 50
E7-8 tg 50

Query:

code:

SELECT (vegetation.cellid), (vegetation.vegcode), (vegetation.coverage)
FROM vegetation
WHERE EXISTS (select vegetation.cellid, vegetation.coverage, vegetation.vegcode FROM vegetation WHERE vegetation.vegcode <> "bs") AND (((vegetation.coverage) IN (select max (vegetation.coverage) 
FROM vegetation
GROUP BY vegetation.cellid))
AND vegetation.vegcode = "sg")

Resultaat:
E7-4 sg 50
E7-7 sg 40

En de query van Jaspertje geeft:

code:

SELECT vegetation.cellid, vegetation.coverage, vegetation.vegcode
FROM vegetation
WHERE vegetation.cellid IN (select vegetation.cellid FROM vegetation WHERE vegetation.vegcode <> "bs")
AND vegetation.coverage IN (select max (vegetation.coverage) FROM vegetation 
WHERE vegetation.vegcode <> "bs")

Resultaat:
E7-1 bs 60
E7-5 b 60

Het goede antwoord moet zijn:
E7-1 sg 30
E7-4 sg 50
E7-6 sg 20
E7-7 sg 40

Query 2 lijkt de goede kant op te gaan. Alleen worden daar de coverage van de cellen (1 en 6) waar "bs" de grootste coverage heeft helemaal niet meer meegenomen. Mss toch beter om de (unieke) id's te selecteren ipv de cellid's.
Ik ga dat iig ff proberen, maar als iemand nog andere suggesties heeft...

[Jaspertje]

En de volgende dan:

SQL:

SELECT distinct vegetation.cellid, max(vegetation.coverage), vegetation.vegcode
FROM vegetation
WHERE vegetation.vegcode <> "bs"
Group by vegetation.cellid, vegetation.vegcode

Weet niet of dat ik wat je wilt... Misschien kan je dat nog een keer formulieren..

[ Voor 21% gewijzigd door Jaspertje op 20-12-2004 15:00 ]

[Verwijderd]

Nope,

Hierbij krijg ik de melding: You tried to execute a query that does not includ the specified expressions 'cellid' (en 'vegcode') as part of an aggregated function.

Als ik 'cellid' en 'vegcode' in een group by zet, krijg ik als resultaat alle (dus niet de maximale) vegetation coverages behalve die van "bs"....

[Verwijderd]

Ik heb dus vegetatie-opnames gemaakt in verschillende cellen. Elke cel heeft verschillende vegetaties (short grass - tall grass - bush - bare soil) met verschillende bedekkingspercentages (40, 50 enz.). Echter, de bedekkingspercentages van "bs"(bare soil) wil ik buiten beschouwing laten.
Dus: Ik wil dus bijv weten in welke cellen "sg" het grootste bedekkingspercentage waarbij "bs"buiten beschouwing is gelaten.

[Verwijderd]

Ik heb nu een soort van omweg gemaakt. Eerst een query gemaakt die een tabel produceert waar "bs" niet in voorkomt en vervolgens gewoon een query geschreven die de hoogste coverage per cellid selecteert. die query had ik al, werkend, niks aan het handje dus.

De tabel:


De query:

code:

SELECT ([without bs].cellid) AS ["nr of cells where sg has largest cover"], ([without bs].vegcode) AS Expr1, ([without bs].coverage) AS Expr2
FROM [without bs]
WHERE ((([without bs].coverage) 
IN (select max ([without bs].coverage) from [without bs] group by [without bs].cellid)) 
AND (([without bs].vegcode)="sg"));

Het resultaat:
E7-1 sg 30
E7-4 sg 50
E7-6 sg 20
E7-7 sg 40
E7-9 sg 15

De eerste 4 zijn goed. De laatste niet. Immers cellid E7-9 heeft ook coverages van: E7-9 tg 40 en E7-9 b 50. Die laatste 2 zijn beiden hoger.
Vreemd, vreemder nog als ik 15 in 16 of bijv 10 verander. Dan loopt hij wel normaal, bij 25 weer niet

Er zou dus iets mis moeten zitten in de query (of in access/SQL maar dat lijkt me niet) maar wat?

[ Voor 10% gewijzigd door Verwijderd op 21-12-2004 13:57 ]