Weird Not In Query

P

Peter Carlson

I'm struggling with this query:
Assuming the following tables
xCerts:
id, certificate, required

xStatus:
id, status, active

Member:
id, last, first, middle, callsign, status

Certs:
id, certificateid, memberid, issued, expires

I need a sql query that shows me for all active members, required
certificates that are either non-existing or expired. For example, lets say
driver's license is a required cert and Peter is a member:
xCerts: id=1, certificate="dl", requred=1
xStatus: id=1, status="active", active=1
Member: last="carlson", first="peter", middle="", callsign="831", status=1

In this case since there is no "Cert" record for certificateid=1 and
memberid=1 it would return my select, OR if it did exist and was expired it
would return the select. The fields I need are:
last, first, middle, callsign from Member
certificate from xCerts
where Member.status = xStatus.id and xStatus.active=1
FOREACH xCerts.id NOT IN Certs FOREACH Member.id

The OR can be a seperate query and a separate table, but all in one would be
nice.

I hope I explained this ok.

Peter
 
Top