Post reply

Issue with Join ...

  • September 1, 2011 at 8:16 am

    #244102

    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

    #1375989

    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

    #1375990

    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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #1376004

    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

    #1376014

    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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #1376022

    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

    #1376110

    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

    #1377247

    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