am i right in thinking this query would return:
>Query Results
Sid Cert Status
Eric Filing A
Eric Ethics A
Tara Ethics A
I am looking for
>Query Results
Sid Cert Status
Eric Filing A
Eric Ethics A
Bill Filing NULL
Bill Ethics NULL
Tara Filing NULL
Tara Ethics A
From: Bryan Love <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: RE: Access SQL
Date: Mon, 13 May 2002 10:18:58 -0700
SELECT
S.Name,
R.Cert,
C.Status,
FROM (C
LEFT OUTER JOIN s
ON S.SID = C.SID)
LEFT OUTER JOIN r
ON C.RID = R.RID
WHERE r.rid is not null
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Timothy Heald [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 13, 2002 9:33 AM
To: CF-Talk
Subject: RE: Access SQL
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
