I am looking for:
select s.name, r.cert
from s, r
as a result set - or you could think of it as a crosstab report. Staff by
Training Types
and I am looking to put the c.status in as a data cell if it exists, and
null if it doesn't
eric
From: "Timothy Heald" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: RE: Access SQL
Date: Mon, 13 May 2002 12:33:12 -0400
Try
SELECT
S.Name,
R.Cert,
C.Status,
FROM C
LEFT JOIN s
ON S.SID = C.SID
LEFT JOIN r
ON C.RID = R.RID
WHERE r.rid is not null
Tim Heald
ACP/CCFD :)
Application Development
www.schoollink.net
> -----Original Message-----
> From: Eric Dawson [mailto:[EMAIL PROTECTED]]
> Sent: Monday, May 13, 2002 12:21 PM
> To: CF-Talk
> Subject: Access SQL
>
>
> starting to get frustrated...
>
> Trying to do something quick but I can't find the syntax to make it work.
>
> 3 tables in access
> S (SID, Name)
> R (RID, Cert)
> C (CID, SID, RID, Status)
>
> sample data.
> S (Eric,Bill,Tara)
> R (Filing,Ethics)
> C (1,Eric,Filing,A,
> 2,Eric,Ethics,A,
> 3,Tara,Ethics,A)
>
> I need to get all the Requirements for each staff and check to
> see if there
> is a certification record. In one Access query...
>
> Something like...
> SELECT
> S.Name,
> R.Cert,
> C.Status,
> FROM S, R LEFT JOIN C ON (R.RID = C.RID and S.SID = C.SID)
>
> to paraphrase: all staff (S) boxed with Requirements (R) with C
> if a record
> exists.
>
> Query Results
> Sid Cert Status
> Eric Filing A
> Eric Ethics A
> Bill Filing NULL
> Bill Ethics NULL
> Tara Filing NULL
> Tara Ethics A
>
> I know there is an easy answer, I just can't find the syntax to do this.
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
