Query Join? Include all records from table

Quote: Originally Posted by JC2710 HI I have 2 tables ProductType and Sales. I am trying write a query which gives me a summary of Product Types and Sales for each Product. Product.....................Sales A..............................100 B...............................50 C...............................250 D................................0 E................................20 However when I run my query I get Product.....................Sales A..............................100 B...............................50 C...............................250 E................................20 Product D is missing because it has no Sales. However I still want it included in results of Query. I have tried left and right joins but they still wont return all the products in the products table? Can anyone help? Thanks

You could use a LEFT JOIN (or RIGHT JOIN)

SELECT Product.Name, Sales.Total
FROM Product
LEFT JOIN Sales
ON Product.Product_ID=Sales.Product_ID

check the following :
W3school SQL LEFT JOIN

Quote: Originally Posted by pod You could use a LEFT JOIN (or RIGHT JOIN) SELECT Product.Name, Sales.Total FROM Product LEFT JOIN Sales ON Product.Product_ID=Sales.Product_ID check the following : W3school SQL LEFT JOIN

I am but it doesnt work. Maybe because I have a WHERE stmt included in the Query?

Quote: Originally Posted by JC2710 I am but it doesnt work. Maybe because I have a WHERE stmt included in the Query?

it's possible. See if you can make it work first without the WHERE statement first.

Hi. To add to Pod's reply, if you remove the where clause and the query does return a value for the missing grouping then the reason you don't see the group when the WHERE clause is present is because your WHERE clause does not work with Null values for the compared fields.

To explain, if there are no rows to return for a particular grouping in an outer-joined query any calculated field on the 'other side' of the outer join for that grouping will be returned as a null. If your WHERE clause was, say, WHERE SALES > 0 this will exclude rows where SALES is null, because nulls are not compatible with any other type of value.

In handling nulls you either have to test for them explicitly, or convert them to typed values which the comparison can handle correctly.

It would be easier to guide you if you posted the SQL of your query, including the WHERE clause, for us to check.

-Stewart

Thanks for your help guys!

My query works without the WHERE

But doesnt include all ProductTypes with WHERE....the WHERE stmt has 2 dates.

So is there any way of using a left join with a WHERE??

Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this:



-Stewart

Quote: Originally Posted by Stewart Ross Inverness Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this: Code: ( text ) WHERE (((tblEstimates.Deleted)=0) AND ( (tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) OR (tblEstimates.DateReceived IS Null) GROUP BY tblProductType.ID, tblProductType.ProductType; -Stewart

Thanks for all your help guys! Works great!

Quote: Originally Posted by JC2710 Thanks for all your help guys! Works great!

HELP!!!!

I cant get this to work!!!

Adding WHERE tblEstimates.DateReceived Is Null works on the query I stated above but I have an almost identical query and it doesnt work!!

Then I add OR tblEstimates.DateReceived IS NULL

And the left join still wont work!!!

So frustrating!! Any ideas?

Quote: Originally Posted by Stewart Ross Inverness Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this: Code: ( text ) WHERE (((tblEstimates.Deleted)=0) AND ( (tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) OR (tblEstimates.DateReceived IS Null) GROUP BY tblProductType.ID, tblProductType.ProductType; -Stewart

Sorry Stewart

Looks like your previous suggestion doesnt work!!

If I add the OR (tblEstimates.DateReceived IS Null) it only brings back ProductTypes that have no estimates related to them at all, not no estimates related to them between the two dates!!

All i want is a simple LEFT JOIN that works!!

Has anyone ever made anything in SQL work apart from SELECT Field FROM table!!

Im starting to wonder!!

Ho hum another cpl of days wasted!!

If you can help Stewart I would be grateful!!

Thanks

Hi. SQL is a very powerful query language - but to use it to good effect we need to be very clear about what we are trying to achieve. I think there has been a lack of clarity, reflected in experiments with SQL which do not achieve what you want. These are not a waste, as you will always learn more when you try to resolve problems for yourself this way.

What you want to achieve:

1. You want to total sales activity for your products between two dates.
2. You want to show all products which DO NOT have any sales totals between two dates.

I did not take into account the dates for (2) when going over the left-join requirements with you, and should have recognised that to satisfy requirements 1 and 2 we need two queries, which can be combined in one SQL statement if necessary, but it is still two queries. The first to calculate the totals, and the second to left-join these back to the products table. Showing these as separate (cascaded) queries based on your earlier posts:

I have named this one qryEstimatesCount, so it can be used in

but it ought to be possible to include these in one SQL query as follows

but I haven't tried this out to check. The two-query cascade approach will certainly do the SELECT the totals you want, then LEFT JOIN this back to the products table as necessary.

-Stewart

Quote: Originally Posted by Stewart Ross Inverness Hi. SQL is a very powerful query language - but to use it to good effect we need to be very clear about what we are trying to achieve. I think there has been a lack of clarity, reflected in experiments with SQL which do not achieve what you want. These are not a waste, as you will always learn more when you try to resolve problems for yourself this way. What you want to achieve: 1. You want to total sales activity for your products between two dates. 2. You want to show all products which DO NOT have any sales totals between two dates. I did not take into account the dates for (2) when going over the left-join requirements with you, and should have recognised that to satisfy requirements 1 and 2 we need two queries, which can be combined in one SQL statement if necessary, but it is still two queries. The first to calculate the totals, and the second to left-join these back to the products table. Showing these as separate (cascaded) queries based on your earlier posts: Code: ( text ) SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates] FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) GROUP BY tblProductType.ID, tblProductType.ProductType; I have named this one qryEstimatesCount, so it can be used in Code: ( text ) SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates] FROM tblProductType LEFT JOIN qryEstimatesCount ON tblProductType.ID = ProductID GROUP BY tblProductType.ID, tblProductType.ProductType; but it ought to be possible to include these in one SQL query as follows Code: ( text ) SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates] FROM tblProductType LEFT JOIN (SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates] FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) GROUP BY tblProductType.ID, tblProductType.ProductType)   ON tblProductType.ID = ProductID GROUP BY tblProductType.ID, tblProductType.ProductType; but I haven't tried this out to check. The two-query cascade approach will certainly do the SELECT the totals you want, then LEFT JOIN this back to the products table as necessary. -Stewart

Thnaks for your help Stewart!

Appreciate it!

Sorry if i sound frustrated!!!

I will tr this out when I get the chance later today!!

Thanks again

Hi. I saw that you had posted a response which may have been edited to be just the letters 'dgfgddfgdfgdfgdfgdfgdfgdfgdfgdf' so I deleted it in my Moderator capacity.

I tried out the one-query Left-join approach myself without success this afternoon, so I may be leading you astray thinking that the two could be combined. I will check this out further and let you know.

If the two-query version is working for you then you do indeed have a viable solution. This is the approach I use myself for all such two-stage queries (do some grouping then left-join results). I think it is easier to follow, and allows for re-use of the first stage totalling query to do other jobs if necessary without rewriting.

-Stewart

Interesting.

Let me know if this works for you. If not, then your first step is to specify as clearly and logically as you can exactly what you require from the SQL. This will help you as well as anyone trying to assist you. It's almost impossible to work logically without a clear and defined understanding of what's required.

As a last point, please remember to post all your code (including SQL) in the code tags provided. This is a requirement on this site, as not doing so wastes so much time and effort for people trying to assist.

Hi Stewart

Thanks again for all your help.

Yes the "dfgfdfgdfdfdfgdfdfg" was me editing my post as I couldnt work out how to delete it! Sorry!

I think Ive solved my problem! Have used your theory, which just needed tweaking slightly.

Anyway this is the final SQL which seems to work! Which combines the 2 queries!



I can now move on to other problems!

Thanks again for all your help. Im sure Ill be needing it again soon!

Thanks

James

Well done James, particularly for sorting out the syntax of the single-query version. Excellent!

-Stewart

Hi Stewart

Afraid Im stuck again!

I have more or less the same problem as above.

I have tblEstimates holding estimates, linked to tblEstimators. Each Estimate has a status ie Work In Progress, Won , Lost. This is linked to tblStatus. Simple so far. So when I do a query to see the status of Estimates for each estimator. I do a simple GROUP query. However I get the same problem as before where it only includes status's for each estimator that exist.

So if the estimator has Won 3 estimates it shows and if they have Lost 2 it shows. But if they have Won 3 and Lost 0, it only shows them as Won 3, and Lost doesnt appear! However the solution above that we worked out wont work because its broken down by each Estimator and you end up with NULL in estimator field. Dont know if this makes sense.

Have included the query below:



So basically I get:

Estimator Name: Smith

Won: 3

Whereas I want:

Estimator Name: Smith

Won: 3

Lost: 0



Any ideas?

Thanks

Solution as before is to separate the two issues - the counting of the number of estimates and the outer-joining of the estimators.

Devise an outer-joined summary query which provides the counts for each estimator by joining the estimates and status tables as in your example SQL, but without including the estimators table in the query. Save this estimate count query. Left-join the count query to the estimators to get your final results, using Nz or similar to convert null values for the counts to zeros.

You will find it much easier to break the problem down into steps which lead on from one to the next than to try to solve this all-in-one, particularly when using outer joins. An advantage is that you can check that the count query is returning correct results before you try outer joining the results back to the estimators.

-Stewart

Hi Stewart

Thanks for the advice! Sorry, I was in one of my SQL moods!!!

I think ive solved it! I did 3 querys. The first to Select All Estimators and All Status's, which returned each status with each estimator. Then I did a group by query to get all estimates and then finally a query that joined the two using NZ as before for the NULLS.

Seems to work. And I figured it out before you posted yr advice!! So im obviously learning from yr excellent teaching!! :o)

Thanks again!

Im sure ill be back!!

James

Good for you James :)

It's always nice to see members visibly progressing and learning as time goes on.

Everyone,

I have found this discussion very helpful in solving essentially the same problem and I wish I had come to this realization a long time ago. I was able to follow the comments left by JC2710 in post 15 of this thread and create a query that gives me the results the way I want them.

However I am running into a problem when I save the query in MS Access. What I input as the SQL code is as follows:



When I reopen the query and view the SQL Code again MS Access has changed it to:



For some reason Access is changing the parentheses () that open and close the nested query T2 into brackets [] and adding a period at the end.

Anyone have a clue why this might be happening?

Any help here is appreciated!

I didn't get it in before the edit timer on the last post but the reason this change is a problem is because it will not allow me to come in and edit the SQL and save it again because it has a syntax error in the FROM clause.

Hi jghouse. The problem you describe is unfortunately an Access bug. it seems to arise with valid SQL statements which Access is unable to show graphically in design view. I know of no fix for it, I'm sorry to say.

On the occasions I edit such statements I have to change the incorrect rectangular bracketing manually.

-Stewart

Quote: Originally Posted by Stewart Ross Inverness Hi jghouse. The problem you describe is unfortunately an Access bug. it seems to arise with valid SQL statements which Access is unable to show graphically in design view. I know of no fix for it, I'm sorry to say. On the occasions I edit such statements I have to change the incorrect rectangular bracketing manually. -Stewart

Stewart,

Thank you for the response. Upon working on this more today I have found that I am able to save it with the rectangular bracketing and period in there now.

The only change I made to get this to work was to remove all other bracketing in the SQL statement. Specifically aroud the portion where I rename the Nz(Sum(...)) As [x] portion. I have removed the brackets around x and now the query will save and can be edited even with the square brackets. I have no clue why this works but it seems to repeat well.

I really wish I could explain how Access reads that SQL with the extra [] and period but it does work, saves, and is editable with no problems.

I have pasted the working code below for reference:

I use the function below when getting SQL from out of a QueryDef.

It's true, there are times in later versions when the square brackets are accepted, but they are not standard SQL even then.

Quote: Originally Posted by NeoPa I use the function below when getting SQL from out of a QueryDef. It's true, there are times in later versions when the square brackets are accepted, but they are not standard SQL even then. Code: ( text ) 'GetSQL gets the SQL component from a named query OR a SQL string. 'When subqueries are specified in MS Access they are changed internally 'from   "FROM (SELECT blah blah blah) AS" to 'either "FROM [SELECT blah blah blah]. AS" 'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL. 'This code assumes any effected subquery will start with " [SELECT ". 'This reverts SQL to correct format and loses ';' at end if requested. ' 11/11/2004    Updated to detect second problem and to use InStr Public Function GetSQL(strQuery As String, _                        Optional blnLoseSC As Boolean = True) As String     Dim intDepth As Integer     Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long       'If param passed is already SQL then leave as is - Otherwise get from query     If Left(strQuery, 1) = "(" Then         GetSQL = strQuery     Else         On Error Resume Next         GetSQL = CurrentDb.QueryDefs(strQuery).SQL     End If     lngLeft = -7     Do         'Check for corrupted subquery         lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)         If lngLeft = 0 Then Exit Do         'To find end correctly we must treat '[' & ']' as matched pairs         intDepth = 1         lngRight = lngLeft + 8         lngOpen = -lngRight         Do             'Find next ']'             lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)             If lngRight = 0 Then                 GetSQL = ""                 Exit Function             End If             intDepth = intDepth - 1             Do                 'For lngOpen negative numbers mean that item has been counted                 'If already counted get next one - Otherwise drop through                 If lngOpen < 0 Then _                     lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)                 'we're only interested (now) if it found one BEFORE the ']'                 If lngOpen > lngRight Or lngOpen = 0 Then Exit Do                 intDepth = intDepth + 1                 lngOpen = -lngOpen             Loop         Loop While intDepth > 0         'If '].' found then be sure to drop the '.' too         lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)         GetSQL = Left(GetSQL, lngLeft) & "(" & _                  Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _             &nb