I'm writing a SQL statement to select a user on a table if that user does not exist on another table, then I want to pull in all their data from another table then I'll be outputting all that in Coldfusion.
How do I do this? I have tried, my atempt is below:
pro_profile == All employees
mod_userStatus == All Professional Staff members
I am doing the not exists to remove professional stuff to just get our student staff.
mod_StudentCertifications is a listing of the certifications they (students) hold.
If we do an AND statement between the where statements it returns no users, or only users that meet both conditions, if we do an OR it will return users with only 1 condition. I need it first Match the not exists then proceed to the exists statement.
Is that do able?
Here's my Statement:
SELECT
profileID,
firstName,
lastName
FROM
pro_Profile
WHERE not exists (
SELECT
profileID
FROM
mod_userStatus
WHERE
mod_userStatus.profileID = pro_Profile.profileID
)
OR
exists (
SELECT
cprAdultExp,
cprInfantChildExp,
cprFPRExp,
aedExp,
firstAidExp,
emtExp,
waterSafetyInstructionExp,
bloodPathogensExp,
oxygenAdminExp,
lifegaurdingExp,
wildernessResponderExp,
notes
FROM
mod_StudentCertifications
WHERE
mod_StudentCertifications.profileID = pro_Profile.profileID
);
