September 1, 2011 at 8:16 am
Hi
I’m wishing to extract data from two tables and use the data in a third table to link them. Joins I hear you say, cool. I’m not that ace at joins, not cool I hear you say…
Here are the 3 table’s relevant columns –
Patient table
dbPatCnt, dbPatFirstName, dbPatLastName, dbStatusID
LnkPhone table
dbPhoneID, dbKeyCnt
Phone table
dbPhoneID, dbPhoneNumber, dbPhoneTypeID
[Patients] dbPatCnt = [LnkPhone] dbKeyCnt
[LnkPhone] dbPhoneID = [Phone] dbPhoneID
I wish to display -
dbPatCnt, dbPatFirstName, dbPatLastName, dbPhoneNumber, dbPhoneTypeID
Conditions –
dbStatusID != 13
dbPhoneTypeID = 5 or 9
Any help and hopefully an explanation or pictures ... would be greatly appreciated...mostly the help tho, thanks
Editor: Removed some content that was inappropriate
p.s. I'm just after the sql to do this..it's going in a php script
September 1, 2011 at 8:26 am
Without seeing the DDL for the tables and knowing the keys, it's hard to give you the exact.
In terms of conditions, use them in a WHERE, something like
WHERE dbstatusID != 13
AND (dbPhoneTypeID = 5 OR dbPhoneTypeID = 9)
The joins would occur by matching up the tables on the columns that are alike. You want to use the key fields, but my guess is something like:
FROM Patients p
INNER JOIN LinkPhone lp
on p.dbPatCnt = lp.dbKeyCnt
INNER JOIN Phone ph
on lp.dbPhoneID = Ph.dbPhoneID
PS - The pictures comment isn't really appropriate. I know it was meant in jest, but there are plenty of people that read the forums that would be offended. I've edited it out.
September 1, 2011 at 8:27 am
Here's the simplest most basic script which makes a lot of assumptions:
SELECT
p.dbPatCnt,
p.dbPatFirstName,
p.dbPatLastName,
f.dbPhoneNumber,
f.dbPhoneTypeID
FROM Patients p
INNER JOIN LnkPhone l ON l.dbKeyCnt = p.dbPatCnt
INNER JOIN Phone f ON f.dbPhoneID = l.dbPhoneID
WHERE p.dbStatusID <> 13
AND f.dbPhoneTypeID IN (5,9)
Some sample data will help if this query doesn't return the results which you need.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 1, 2011 at 8:48 am
Thanks guys for the speedy response. I actually used something similar using WHERE instead and it's returned the same amount of rows. However both WHERE and INNER JOIN seems to return the odd duplicated row.
I just look into it a bit more first ...is there an easy way of uploading sample data or is that a stupid question?
September 1, 2011 at 8:58 am
mattech06 (9/1/2011)
Thanks guys for the speedy response. I actually used something similar using WHERE instead and it's returned the same amount of rows. However both WHERE and INNER JOIN seems to return the odd duplicated row.I just look into it a bit more first ...is there an easy way of uploading sample data or is that a stupid question?
Not at all. Read the link "this" below. It provides forum etiquette guidelines as well as showing how to provide sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 1, 2011 at 9:04 am
Thanks again, I've traced the duplication back to where more than one patient does have only one phone number ..makes sense when the extra patients could be the children.
Is there a way to mark this post as complete?
September 1, 2011 at 12:25 pm
mattech06 (9/1/2011)
Thanks again, I've traced the duplication back to where more than one patient does have only one phone number ..makes sense when the extra patients could be the children.Is there a way to mark this post as complete?
Just your post is fine. Glad we could help. If you need more help on this, post again. If it's another question, feel free to start another thread.
September 6, 2011 at 3:27 am
Just as an aside, seeing as you are a join noobee
This will only return patients WITH telephone numbers. If you want all patients AND the telephone number if it exists, you would need to replace the INNER JOINs with LEFT JOINs and move any filters on the Phone table in the WHERE clause to the join criteria
SELECT
p.dbPatCnt,
p.dbPatFirstName,
p.dbPatLastName,
f.dbPhoneNumber,
f.dbPhoneTypeID
FROM
Patients p
LEFT JOIN
LnkPhone l ON l.dbKeyCnt = p.dbPatCnt
LEFT JOIN
Phone f ON f.dbPhoneID = l.dbPhoneID
AND f.dbPhoneTypeID IN (5,9)
WHERE
p.dbStatusID <> 13
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply