Professional Web Applications Themes

Using a Left outer Join on Three Tables - Microsoft SQL / MS SQL Server

Hi I have three tables that I would like to join Query_Table QT Contract_Table CT Usage_Table UT I will always have data in the QT table, and sometime a match in the CT table and sometimes a match in the UT table or a match in the UT table and not in the CT table. it's either one the other both or none. What I have is:- Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date, ut.Read_Date, ut.Meter_Read, ut.Meter_ID From Query_Table AS qh Left Outer Join Contract_Table As ct On qh.Site_No = ct.Site_no Left Outer Join Usage_Table As ut on qh.site_no = ut.site_no and ...

  1. #1

    Default Using a Left outer Join on Three Tables

    Hi

    I have three tables that I would like to join

    Query_Table QT
    Contract_Table CT
    Usage_Table UT


    I will always have data in the QT table, and sometime a match in the CT
    table and sometimes a match in the UT table
    or a match in the UT table and not in the CT table. it's either one the
    other both or none.


    What I have is:-
    Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date,
    ut.Read_Date, ut.Meter_Read, ut.Meter_ID
    From Query_Table AS qh

    Left Outer Join
    Contract_Table As ct
    On qh.Site_No = ct.Site_no

    Left Outer Join
    Usage_Table As ut
    on qh.site_no = ut.site_no and ut.read_date =
    ct_Contract_Start_Date

    Where qh.status = 0

    What Happens is I only get a result if there is a match on both
    "qh.site_no = ut.site_no" and "ut.read_date = ct_Contract_Start_Date"
    sometimes there is not always a read date that was taken on the same day of
    the contract start date.
    I have changing the "AND" to an "OR" but then I get matches on all of the
    read date. Plus I don't want it to match anything other that the read date
    that matches the contract start date, but I do need it to match the site
    number as I need the Meter ID.

    Thanks for you help

    Mark :o)




    Sh0t2bts Guest

  2. #2

    Default Re: Using a Left outer Join on Three Tables

    Tom,

    I'm been a little fick here but what does DDL stand for?


    Thanks

    Mark :o)

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:eYe%phx.gbl...
    If you post your DDL + INSERT's of sample data, we can test proposed solutions. However, you may want to change your join criteria:

    Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date,
    ut.Read_Date, ut.Meter_Read, ut.Meter_ID
    From Query_Table AS qh

    Left Outer Join
    Contract_Table As ct
    On qh.Site_No = ct.Site_no

    Left Outer Join
    Usage_Table As ut
    on ct.site_no = ut.site_no and ut.read_date =
    ct_Contract_Start_Date

    Where qh.status = 0


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Sh0t2bts" <com> wrote in message news:phx.gbl...
    Hi

    I have three tables that I would like to join

    Query_Table QT
    Contract_Table CT
    Usage_Table UT


    I will always have data in the QT table, and sometime a match in the CT
    table and sometimes a match in the UT table
    or a match in the UT table and not in the CT table. it's either one the
    other both or none.


    What I have is:-
    Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date,
    ut.Read_Date, ut.Meter_Read, ut.Meter_ID
    From Query_Table AS qh

    Left Outer Join
    Contract_Table As ct
    On qh.Site_No = ct.Site_no

    Left Outer Join
    Usage_Table As ut
    on qh.site_no = ut.site_no and ut.read_date =
    ct_Contract_Start_Date

    Where qh.status = 0

    What Happens is I only get a result if there is a match on both
    "qh.site_no = ut.site_no" and "ut.read_date = ct_Contract_Start_Date"
    sometimes there is not always a read date that was taken on the same day of
    the contract start date.
    I have changing the "AND" to an "OR" but then I get matches on all of the
    read date. Plus I don't want it to match anything other that the read date
    that matches the contract start date, but I do need it to match the site
    number as I need the Meter ID.

    Thanks for you help

    Mark :o)




    Sh0t2bts Guest

  3. #3

    Default Re: Using a Left outer Join on Three Tables

    DDL = Data Definition Language, eg CREATE TABLE, CREATE PROC, etc.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "Sh0t2bts" <com> wrote in message news:%phx.gbl...
    Tom,

    I'm been a little fick here but what does DDL stand for?


    Thanks

    Mark :o)

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:eYe%phx.gbl...
    If you post your DDL + INSERT's of sample data, we can test proposed solutions. However, you may want to change your join criteria:

    Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date,
    ut.Read_Date, ut.Meter_Read, ut.Meter_ID
    From Query_Table AS qh

    Left Outer Join
    Contract_Table As ct
    On qh.Site_No = ct.Site_no

    Left Outer Join
    Usage_Table As ut
    on ct.site_no = ut.site_no and ut.read_date =
    ct_Contract_Start_Date

    Where qh.status = 0


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Sh0t2bts" <com> wrote in message news:phx.gbl...
    Hi

    I have three tables that I would like to join

    Query_Table QT
    Contract_Table CT
    Usage_Table UT


    I will always have data in the QT table, and sometime a match in the CT
    table and sometimes a match in the UT table
    or a match in the UT table and not in the CT table. it's either one the
    other both or none.


    What I have is:-
    Select Distinct qh.query_no, qh.query_date, ct.Contract_Start_date,
    ut.Read_Date, ut.Meter_Read, ut.Meter_ID
    From Query_Table AS qh

    Left Outer Join
    Contract_Table As ct
    On qh.Site_No = ct.Site_no

    Left Outer Join
    Usage_Table As ut
    on qh.site_no = ut.site_no and ut.read_date =
    ct_Contract_Start_Date

    Where qh.status = 0

    What Happens is I only get a result if there is a match on both
    "qh.site_no = ut.site_no" and "ut.read_date = ct_Contract_Start_Date"
    sometimes there is not always a read date that was taken on the same day of
    the contract start date.
    I have changing the "AND" to an "OR" but then I get matches on all of the
    read date. Plus I don't want it to match anything other that the read date
    that matches the contract start date, but I do need it to match the site
    number as I need the Meter ID.

    Thanks for you help

    Mark :o)




    Tom Guest

Similar Threads

  1. left outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  2. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  3. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  4. SQL query with a Left outer Join
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 04:48 AM
  5. Replies: 2
    Last Post: September 18th, 09:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139