Nested join syntax problems 1

[ProtocolPirate]

This query works:
SELECT p.AcntNumber, p.Name, d.FromDate
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber

But this does not:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
INNER JOIN Charge c ON c.Id=d.ChargeNum

Nor this:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
(INNER JOIN Patient AS p
(INNER JOIN Charge c ON c.Id=d.ChargeNum)
ON d.AcntNumber=p.AcntNumber)

How do you word a nested join?

 

[lespaul]

do you get an error with this:
[tt]SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
INNER JOIN Charge c ON c.Id=d.ChargeNum[/tt]

I don't see anything that would prevent this from working....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ProtocolPirate]

Syntax error (missing operator) in query expression ‘d.AcntNumber=p.AcntNumber INNER JOIN Charge c ON c.Id=d.ChargeNum’.

 

[ProtocolPirate]

No matter how I swap things around the error always start immediately after the first ON clause.

 

[lespaul]

ok I take that back, does access require the AS?? it's missing:
[tt]
SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
INNER JOIN Charge As c ON c.Id=d.ChargeNum[/tt]

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ProtocolPirate]

No, it doesn't require the AS. The simple statements still work with it out and the more complex statement still doesn't work with the AS added in for Charge c.

 

[PHV]

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM [!]([/!]Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber[!])[/!]
INNER JOIN Charge c ON c.Id=d.ChargeNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886