Constructing a Selective Join Query
Posted on 2010-01-10
I have two tables,
one called people which records a person's base details, like name and address, and which includes the following fields
PLrn - the record number
PLemail - the person's email address
PLsubscribe - the subscription flag
and one called peoplelink which sets their membership level, which can be different for each subdomain they belong to, and which contains the following fields
PKprn - the person's record number from the people table
PKsubdomain - the subdomain they are a member of
PKlevel - the membership level
I want to write a single query that joins these two together so that I get the email address [from the people table] and the membership level [from the peoplelink table] ONLY for members who belong to a nominated subdomain.
I have tried the following, but it doesn't work, and I suspect is completely wrong...
SELECT * FROM people WHERE people.PLsubscribe = 'Y'
LEFT JOIN peoplelink ON peoplelink.PKprn = people.PLrn WHERE peoplelink.PKsubdomain = 'somesubdomain';
I'm having a great deal of difficulty getting my head around this one... the complication, to me, is that there's conditional selection from both tables.
Any assistance would be gratefully accepted.