A question on JOINS 2

[Trudye]

What is the difference between the JOINS in these two statements. I guess what I'm asking is what are the rules of engagement or hierarchy for JOINS? One JOIN works (1st one) and the second one gets a JOIN error. The only difference I can see is the FROM and JOINs are expressed differently.

strSQL = "SELECT MAX(cost) AS MaxPhoto11x14, [Deal Detail].Item " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& "WHERE Clientid = " & sID & " AND Item = '11x14 Photo'"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxPhoto11x14
GoSub Write_Recs
End If

strSQL = "SELECT MAX(cost) AS MaxPhoto11x14, [Deal Detail].Item " _
& "FROM ([Deal Detail] " _
& "INNER JOIN Clients on Deals.ClientId = Clients.ID) " _
& "INNER JOIN Deals on [Deal Detail].Dealid = Deals.Dealid " _
& "WHERE Clientid = " & sID & " AND Item = '11x14 Photo'"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxPhoto11x14
GoSub Write_Recs
End If

The relationships are
Client is linked to Deals based on ID/ClientID
Deal is linked to [Deal Detail] based on DealID

The hierarchy is client, Deals sits below client and [Deal Detail] sits below Deals.

What is the rule of thumb (if there is one) here?

Thanks much,
Trudye

 

[Golom]

None. An inner join is commutative. That is

A INNER JOIN B

will give the same result as

B INNER JOIN A

Outer joins however are not.

A LEFT JOIN B

is not the same as

B LEFT JOIN A.

You are getting an error because Deals is defined in the second join (outside the parentheses) but you are referencing it in the first join inside them. A reference like this

FROM (A ...) ... ON A.X = ...

is legal but one like this

From ( ... ON A.X = ...) INNER JOIN A ...

is not.

 

[lespaul]

Here's a great article, Understanding SQL Joins, that should help.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Trudye]

Thanks Golom for the feedback, and thanks to lespaul for the link

Be well,
Trudye