Problem with Join 1

[Trudye]

Hi Guys, got a SQL problem.

I have three tables they are linked as such
Client links to Deals via client.iid = Deals.Clientid
Deals links to DealDetail_Hdr via Deals.Dealid = DealDetail_Hdr.Dealid

I am trying to write a SQL statement that will read the Clients tbl and for each client read will return the max cost for item=card(s). The item Card(s) and its cost are kept on the DealDetail_Hdr tbl.

Here is what I came up with:
strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "FROM Clients " _
& "INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID " _
& "Inner JOIN Deals ON Clients.ID = Deals.ClientId " _
& " WHERE clientid = " & sID & " And Item = " & "Card(s)"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sCards = rst!MaxCards
write_rec = -1
End If

I am getting an error msg that there is a problem with the Join portion of the statement.

Any ideas?

Thanx
Trudye

 

[r937]

access requires its joins to be parenthesized

select max(DealDetail_Hdr.COST) as MaxOfCOST
     , DealDetail_Hdr.Item 
  from [COLOR=red][b]([/b][/color]
       Clients 
inner 
  join Deals 
    on Clients.ID = Deals.ClientId 
       [COLOR=red][b])[/b][/color]
inner 
  join DealDetail_Hdr 
    on Deals.DealID = DealDetail_Hdr.DEALID 
 where clientid = [i]foo[/i]
   and Item = [i]bar[/i]
group
    by DealDetail_Hdr.Item

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Trudye]

Thanx r937 for the help, your response got me around the error.

Now its saying I have a syntax error in the FROM Clause. It looks ok to me. The is the way it looks in the Immediate Widow.

SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item FROM Clients (INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID) (Inner JOIN Deals ON Clients.ID = Deals.ClientId) WHERE clientid = 8 And Item = Card(s)

Thanx again,
Trudye
Trudye

 

[r937]

no, you didn't parenthesize them correctly, and you forgot the GROUP BY

select max(DealDetail_Hdr.COST) AS MaxOfCOST
     , DealDetail_Hdr.Item 
  from (
       Clients 
inner 
  join Deals 
    on Clients.ID = Deals.ClientId
       )       
inner 
  join DealDetail_Hdr 
    on Deals.DealID = DealDetail_Hdr.DEALID
 where clientid = 8 
   and DealDetail_Hdr.Item = ???Card(s)???
group
    by DealDetail_Hdr.Item

what is this Card(s)? is that the actual name of the column? or is that a variable that you forgot to substitute?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Trudye]

That is what is reflected in the [blue]Immediate Widow [/blue] after variables have been substituted,
(ie. ? StrSQL).

The original SQL statement looked exactly like the first statement I sent you (minus the parenthesis of course).

Thanks,
Trudye

 

[r937]

run your query outside of asp or vb or whatever language that is

i.e. paste the pure sql into access SQL View

then run it and see what access says

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Trudye]

That's funny, that's exactly where I got it from. I just replaced the Having statement with the Where statement.

But I'll try it. What the heck it can't hurt.

Thanx
Trudye

 

[PHV]

And this ?
strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "FROM (Clients " _
& "INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID) " _
& "INNER JOIN Deals ON Clients.ID = Deals.ClientId " _
& " WHERE clientid = " & sID & " And Item = 'Card(s)'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

PHV, you forgot the GROUP BY too

also, maybe this is okay, but it sure looks wonky, look inside your parentheses, you're joining A to B on C.x = B.y, and then later joining C on A.p = C.q

like i said, maybe that'll work, but it just looks wrong

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

Thanks rudy, I guess I need lore coffee...
I think the GROUP BY clause is not necessary due the WHERE clause:
strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "FROM (Clients " _
& "INNER JOIN Deals ON Clients.ID = Deals.ClientId) " _
& "INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID " _
& " WHERE clientid = " & sID & " And Item = 'Card(s)'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

last time i ran an access query with both an aggregate like MAX() and a non-aggregate column in the SELECT list, the GROUP BY on the non-aggregate was mandatory, regardless of the fact that the WHERE clause might restrict rows to having only one value of the non-aggregate

you don't get a syntax error in those situations?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

I must confess you're absolutely right rudy ...

 

[Trudye]

Thanks Rudy and PHV for responding.

Rudy, the relationships are:
Client links to Deals via client.id = Deals.Clientid
Deals links to DealDetail_Hdr via Deals.Dealid = DealDetail_Hdr.Dealid

So that is the way I Joined them in the Join statement. Client to Deals and Deals to DealDetail.

However I'm still getting errors. It seems Access doesn't like the FROM statement which probably means the Join statement(s).

Here is the Access statement
[blue}strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "FROM Clients " _
& "(INNER JOIN Deals ON Clients.ID = Deals.ClientId) " _
& "(INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID) " _
& "GROUP BY DealDetail_Hdr.Item, Clients.ID" _
& " WHERE clientid = " & sID & " And Item = " & "Card(s)"""
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sCards = rst!MaxCards
write_rec = -1
End If [/blue}

Here is the statement from the Immediate Window
[green]? strsql
SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item FROM Clients (INNER JOIN DealDetail_Hdr On Deals.DealID = DealDetail_Hdr.DEALID) (Inner JOIN Deals ON Clients.ID = Deals.ClientId) GROUP BY DealDetail_Hdr.Item, Clients.ID, Item WHERE clientid = 2 And Item = Card(s)"
[/green]

Should I make a subquery? There should be a way to do this in one query shouldn't there? The WHERE and GROUP BY seem to be working. So the problem has to be in the JOIN.

Thanks Guys
Trudye

 

[r937]

do you see where you have FROM Clients (INNER JOIN DealDetail_Hdr?

i wouldn't use the parentheses that way

please try it exactly like this --

  from (
       Clients 
inner 
  join Deals 
    on Clients.ID = Deals.ClientId
       )       
inner 
  join DealDetail_Hdr 
    on Deals.DealID = DealDetail_Hdr.DEALID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Trudye]

Ok Cool we got past the Joins. Now it doesn't like the WHERE statement:

[blue]strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "from (Clients " _
& "inner Join Deals on Clients.ID = Deals.ClientId) " _
& "inner Join DealDetail_Hdr " _
& "on Deals.DealID = DealDetail_Hdr.DEALID " _
& "GROUP BY DealDetail_Hdr.Item, Clients.ID" _
& " WHERE clientid = " & sID & " And Item = " & "Card(s)" [/blue]

Card(s) is a variable found in the field Item
sID is an Integer

Thanks much Rudy

 

[lespaul]

your Card(s) needs to be in quotes:

strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "from (Clients " _
& "inner Join Deals on Clients.ID = Deals.ClientId) " _
& "inner Join DealDetail_Hdr " _
& "on Deals.DealID = DealDetail_Hdr.DEALID " _
& "GROUP BY DealDetail_Hdr.Item, Clients.ID" _
& " WHERE clientid = " & sID & " And Item = " & "[COLOR=red]'[/color]Card(s)[COLOR=red]'[/color]"

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[Trudye]

Access did not like the single quote inside the double quote, it wouldn't compile. So I changed it, it compiled but I am still getting the same error.

Syntax error (missing operator) in query.
'clients.id where clientid = 2 AND Item = 'Card(s)"

Access:
[blue]strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item " _
& "from (Clients " _
& "inner Join Deals on Clients.ID = Deals.ClientId) " _
& "inner Join DealDetail_Hdr " _
& "on Deals.DealID = DealDetail_Hdr.DEALID " _
& "GROUP BY DealDetail_Hdr.Item, Clients.ID" _
& " WHERE clientid = " & sID & " And Item = '" & strCards & "'" [/blue]


Immediate Window:
[green]? strsql
SELECT Max(DealDetail_Hdr.COST) AS MaxOfCOST, DealDetail_Hdr.Item from (Clients inner Join Deals on Clients.ID = Deals.ClientId) inner Join DealDetail_Hdr on Deals.DealID = DealDetail_Hdr.DEALID GROUP BY DealDetail_Hdr.Item, Clients.ID WHERE clientid = 2 And Item = 'Card(s)' [/green]

 

[traingamer]

Put the GROUP BY clause AFTER the WHERE clause. Don't you just love Access?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Trudye]

Thank you, thank you sooo much it worked.

Don't you just love Access?

UHUH, Yea right!

Be well,
Have a great weekend
Trudye

 

[r937]

Put the GROUP BY clause AFTER the WHERE clause. Don't you just love Access?

actually, all database systems insist on the GROUP BY coming after the WHERE clause


how did Clients.ID get added to the GROUP BY?

you'll notice that i had the correct join and the correct sequence of WHERE and GROUP BY in post #2 of this thread





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)