[MS ACCESS] uitdagende join met ms access

[Verwijderd]

Hey mensen,
Ik ben al een hele poos bezig om een bepaade selectie te maken in een MS Access database en ik kan er maar niet uitkomen. Ik zal eerst even de entiteiten en attributen opschrijven voor ik verder ga:

Customer(CustomerID,Info)
MailValue(MailValueID,MailHeadItemID,Description)
MailCust(MailCustID,MailValueID,CustomerID)
Treatment(TreatmentID, CustomerID)
PreOp(PreOpID,TreatmentID,TreatmentControlTypeID)

Korte uitleg over de samenstelling van de tabellen en wat ik nou daadwerkelijk wil bereiken.
In de customer tabel worden alle gegevens van een klant bijgehouden, waaronder van belang voor mijn query een date field -> Info en uiteraard een uniek nummer CustomerID
De MailValue tabel is puur een koppel tabel met hierin een aantal mogelijkheden waaruit een klant kan kiezen, alle met een uniek nummer en een beschrijving. De mogelijkheden zijn ook nog ingedeeld in hoofdgroepen (MailHeadItemID).
In MailCust staan de keuzes die klanten gemaakt hebben uit de MailValues. Dus hier wordt een CustomerID gekoppelt aan een gekozen MailValueID.
In de treatment tabel staan klanten gekoppelt aan een eventuele behandeling en de klant krijgt in deze tabel een uniek behandelnummer.
PreOp is weer gekoppelt aan de treatment tabel, een klant kan meerdere keren gekoppelt zijn aan een preop omdat er verschillende mogelijkheden zijn (TreatmentControlTypeID)

Zo nu dat een beetje duidelijk is, wat probeer ik te bereiken.
Ik wil van elke mailvalue uit de groep mailheadvalueid = 7 een count hoevaak deze voorkomt in het klantenbestand met deze condities:
- de klant heeft een datum bij info van het jaar 2007
- de klant heeft een rij in preop met TreatmentControlTypeID = 1

Nu is dit opzich helemaal niet moeilijk met wat joins ben je er zo, echter het probleem waar ik tegenaan loop is dat niet alle mailvalue's ooit ingevuld zijn en voor diegene die nooit voorkomen wil ik ook een count van 0 hebben. En met gewone joins zal er nooit een rij in de resultaat query staan met die mailvalueid's en zal er dus ook geen count van 0 uitkoment.
Ik heb zelf al zitten denken aan een full outer join, alleen is dit niet mogelijk met ms access.

Dit is wat ik tot nu toe zelf heb:

code:

SELECT Description, count(LaserTreatmentControlType) FROM
(SELECT *
FROM (((SELECT MailValueID, Description FROM MailValue WHERE MailHeadItemId = 22)  AS omschrijving LEFT JOIN (SELECT MailValueID, CustomerID FROM MailCust WHERE MailHeadItemId = 22)  AS gevonden ON omschrijving.MailValueID = gevonden.MailValueID) 
LEFT JOIN (SELECT CustomerID, LaserTreatmentID FROM (SELECT * FROM LaserTreatment 
INNER JOIN (SELECT CustomerID FROM Customer WHERE LaserInfo IS NOT NULL AND YEAR(LaserInfo) = 2007)  AS customer ON LaserTreatment.CustomerID = customer.CustomerID))  AS treatment ON gevonden.CustomerID = treatment.CustomerID) 
LEFT JOIN (SELECT DISTINCT LaserTreatmentID, LaserTreatmentControlTypeID FROM laserPreOp WHERE YEAR(RecDateTime) = 2007 AND LaserTreatmentControlTypeID = 10)  AS preop ON treatment.LaserTreatmentID = preop.LaserTreatmentID);
GROUP BY Description

Alle hulp of gedachtes over het oplossen van dit probleem zijn welkom! Trouwens de rede dat ik alle mailvalues erin wil hebben is omdat ik het resultaat automatisch in excel laat zetten en om te weten welke waardens bij welke description komen moet de lijst altijd even lang zijn.

[jvdmeer]

Verwijderd schreef op dinsdag 03 juni 2008 @ 12:18:
[...]
Nu is dit opzich helemaal niet moeilijk met wat joins ben je er zo, echter het probleem waar ik tegenaan loop is dat niet alle mailvalue's ooit ingevuld zijn en voor diegene die nooit voorkomen wil ik ook een count van 0 hebben. En met gewone joins zal er nooit een rij in de resultaat query staan met die mailvalueid's en zal er dus ook geen count van 0 uitkoment.
[...]

Dit kan je oplossen door een UNION uit te voeren met de set waar geen mailvalueid zijn ingevuld.

[Verwijderd]

maar als ik eerst die eerste selectie maak en daarna die met de lege mailvalueid's en dan een union van die twee dan is de volgorde waarin de rijen met mailvalueid's komen te staan afhankelijk van welke er wel en niet zijn ingevuld.. en dan is het dus niet te voorspellen in mijn excel sheet in welke rij welke gegevens komen..
Er moet door die GROUP BY Description altijd dezelfde volgorde uitkomen, los van welke er wel of niet op 0 uitkomen

[bigbeng]

Wat voor mij bij Access altijd goed werkte is onderdelen van je resultaatset uitbesteden aan een aparte query en hier op joinen.

Maar je wilt dus al je klanten zien, waarbij iedereen die niet voldoet aan je voorwaarden een count van 0 krijgen?
Of wil je alleen de klanten die geen mailheadvalueid=7 hebben, maar wel aan de overige voorwaarden voldoen zien met een count van 0?

[_js_]

Maak eerst een lijst van de CustomerIds die aan de eis voldoen.
Aan de hand daarvan lijstje met mailvalues en customerIds
SELECT MailValueId, CustomerId
FROM Customer
JOIN MailCust ON ...
WHERE CustomerId IN (
SELECT ...
FROM Treatment
JOIN PreOp ON ...
WHERE ...
)
Right join dat hele gebeuren tegen het lijstje van MailValues dat je wilt zien, en je hebt je resultaat.

[Verwijderd]

hmm ik denk dat daar de oplossing in zit _js_ Ik ga het eens uitproberen

[Verwijderd]

Met dank aan JS! Heb ik de oplossing kunnen vinden:

code:

SELECT count(LaserTreatmentControlTypeID)
FROM
(SELECT customers.CustomerID, MailValueID, treatment.LaserTreatmentID, LaserTreatmentControlTypeID
FROM (((SELECT CustomerID FROM Customer WHERE YEAR(LaserInfo) = 2007) AS customers 
INNER JOIN (SELECT CustomerID, MailValueID FROM MailCust WHERE MailHeadItemID = 22) AS mailitems ON customers.CustomerID = mailitems.CustomerID)
INNER JOIN (SELECT CustomerID, LaserTreatmentID FROM LaserTreatment) AS treatment ON customers.CustomerID = treatment.CustomerID)
INNER JOIN (SELECT LaserTreatmentID, LaserTreatmentControlTypeID FROM laserPreOp) AS preop ON treatment.LaserTreatmentID = preop.LaserTreatmentID
WHERE LaserTreatmentControlTypeID = 1) AS part1 
RIGHT JOIN (SELECT MailValueID, Description FROM MailValue WHERE MailHeadItemID = 22) AS part2 ON part1.MailValueID = part2.MailValueID
GROUP BY Description

Enorm bedankt voor jullie hulp