query with 2 link tables, needing a SUM with 0 for no records!

[gangel]

Hey everyone,

So i have a confusing query that is screwed perhaps because i have been looking at it for so long.

The query is based on info for a program im making for wife.

She notes what meals she has every day
The program notes the meals, which contain "serves" of categories

eg.
Roast chicken
1 serve protein
2 serves vegetables etc etc

She then records the meals she ate on each day and the program knows which categories she hasnt had enough of!!!

The attached pic the query at the moment... which shows how many categories she has on each day, but i need this to say 0 for the categories she hasnt had any of.!!

I have done this previously with subqueries for a different program, but i think im getting lost with the 2 link tables



Any help/advice would be great, im totally confusing myself with this!

[CJ_London]

not sure which table it will apply to (perhaps tbCategories?) but you will need a left join to list all the categories - which means all the other joins 'down the chain' will need to be left joins.

Basically looks like you have just accepted the links as defined by the relationships rather than considered the data you need to display. Although they look the same, links in queries do not need to mirror relationships which are more to do with maintaining data integrity

[gangel]

Hi Ajax
I actually tried a few different types and just put the standard result up for the forum.

This is All left joins:


i think im at a loss. I might have to add a record for linkcatmeal for every mealid and categoryid and have serves as 0 value... because i just cant get this query to work!

[CJ_London]

which shows how many categories she has on each day, but i need this to say 0 for the categories she hasnt had any of.!!

OK, we'll try something different

first, create an Cartesian query (no joins) - call it Qall

SELECT *
FROM tblDates, tblCategories

this will show all categories for all dates

now create a second query to show what has actually been served on each day - call it Qactual

SELECT linkdatemeal.dateID, linkCatMeal.*
FROM linkdatemeal INNER JOIN linkCatMeal

now create a third query to link these two queries together

SELECT Qall.recdate, Qall.catname, sum (nz(Qactual.serves,0)) as servings
FROM Qall LEFT JOIN Qactual ON Qall.DateID=Aactual.dateID and Qall.catID=Aactual.catID
GROUP BY Qall.recdate, Qall.catname

The above is freetyped so may contain typos, but give it a try.

[gangel]

yep typos buit OMG THANK YOU

tbh im so happy i have learnt how to do this. My brain just couldnt fit around it at all!

[CJ_London]

if you wanted to do it in one query then you would have

SELECT Qall.recdate, Qall.catname, sum (nz(Qactual.serves,0)) as servings
FROM (SELECT *FROM tblDates, tblCategories) Qall LEFT JOIN (SELECT linkdatemeal.dateID, linkCatMeal.*
FROM linkdatemeal INNER JOIN linkCatMeal) Qactual ON Qall.DateID=Aactual.dateID and Qall.catID=Aactual.catID
GROUP BY Qall.recdate, Qall.catname