Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Constructing a Selective Join Query

Posted on 2010-01-10
Medium Priority
Last Modified: 2013-12-12
Hi All,
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.

Question by:chestertbear
  • 2

Expert Comment

ID: 26280784
Hi There,

There should only be a single WHERE clause for the query, which needs to go to the end.

Try this:

SELECT *FROM   people LEFT JOIN peoplelink ON peoplelink.PKprn = people.PLrn
WHERE peoplelink.PKsubdomain = 'somesubdomain' AND people.PLsubscribe = 'Y'

Accepted Solution

pauly5 earned 400 total points
ID: 26280787
The join type should be INNER JOIN not LEFT JOIN if you only want the rows for the specified subdomain and where their subscription is enabled.

SELECT * FROM   people INNER JOIN peoplelink ON peoplelink.PKprn = people.PLrn
WHERE peoplelink.PKsubdomain = 'somesubdomain' AND people.PLsubscribe = 'Y'
LVL 41

Assisted Solution

Sharath earned 100 total points
ID: 26280984
Instead of * in the SELECT clause of the query provided by pauly5, you can mention the columns of your choice.

SELECT PLEmail,PKLevel FROM   people INNER JOIN peoplelink ON peoplelink.PKprn = people.PLrn
WHERE peoplelink.PKsubdomain = 'somesubdomain' AND people.PLsubscribe = 'Y'

Author Closing Comment

ID: 31675348
Brilliant.  Thank you both so much.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question