Professional Web Applications Themes

Returning results where the match is not always there!!! - Microsoft SQL / MS SQL Server

Hi All I have two tables that I need to match customer data on. The first table holds all of the customers billing info Title Name Address Account Number The second holds amongst other things the customer e-mail address, dont ask why they are seperate it's not my database, Account number e-mail address etc etc In my where statment I match on the account number as that is the only thing they have in common, "Where tbl1.Account_Number = tbl2.Account_Number) but not everyone has an e-mail address. I only get results where there is a true match, how can I say ...

  1. #1

    Default Returning results where the match is not always there!!!

    Hi All

    I have two tables that I need to match customer data on.
    The first table holds all of the customers billing info

    Title
    Name
    Address
    Account Number

    The second holds amongst other things the customer e-mail address, dont ask
    why they are seperate it's not my database,

    Account number
    e-mail address
    etc
    etc

    In my where statment I match on the account number as that is the only thing
    they have in common, "Where tbl1.Account_Number = tbl2.Account_Number) but
    not everyone has an e-mail address.

    I only get results where there is a true match, how can I say (select
    e-mail_address Where tbl1.Account_Number = tbl2.Account_Number) but if there
    is no match still return the results from the first table??


    Hope this makes sence

    Mark :o)


    Sh0t2bts Guest

  2. #2

    Default Re: Returning results where the match is not always there!!!

    You could use a LEFT JOIN


    SELECT t1.title, t1.name, t1.address,
    t1.[account number], t2.[e-mail address]
    FROM table1 t1 LEFT JOIN table2 t2
    ON t1.[account number] = t2.[account number]


    The e-mail address column should be null when there is no valid e-mail
    address in table2...





    "Sh0t2bts" <sh0t2btshotmail.com> wrote in message
    news:utEiMv0PDHA.1336TK2MSFTNGP11.phx.gbl...
    > Hi All
    >
    > I have two tables that I need to match customer data on.
    > The first table holds all of the customers billing info
    >
    > Title
    > Name
    > Address
    > Account Number
    >
    > The second holds amongst other things the customer e-mail address, dont
    ask
    > why they are seperate it's not my database,
    >
    > Account number
    > e-mail address
    > etc
    > etc
    >
    > In my where statment I match on the account number as that is the only
    thing
    > they have in common, "Where tbl1.Account_Number = tbl2.Account_Number)
    but
    > not everyone has an e-mail address.
    >
    > I only get results where there is a true match, how can I say (select
    > e-mail_address Where tbl1.Account_Number = tbl2.Account_Number) but if
    there
    > is no match still return the results from the first table??
    >
    >
    > Hope this makes sence
    >
    > Mark :o)
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Returning results where the match is not always there!!!

    Mark:

    You can do:

    select e-mail_address, [everything else you want]
    from tbl1
    left join tbl2
    on tbl1.Account_Number = tbl2.Account_Number

    The matches that don't have e-mail addresses will return
    null.
    >-----Original Message-----
    >Hi All
    >
    >I have two tables that I need to match customer data on.
    >The first table holds all of the customers billing info
    >
    >Title
    >Name
    >Address
    >Account Number
    >
    >The second holds amongst other things the customer e-mail
    address, dont ask
    >why they are seperate it's not my database,
    >
    >Account number
    >e-mail address
    >etc
    >etc
    >
    >In my where statment I match on the account number as
    that is the only thing
    >they have in common, "Where tbl1.Account_Number =
    tbl2.Account_Number) but
    >not everyone has an e-mail address.
    >
    >I only get results where there is a true match, how can
    I say (select
    >e-mail_address Where tbl1.Account_Number =
    tbl2.Account_Number) but if there
    >is no match still return the results from the first
    table??
    >
    >
    >Hope this makes sence
    >
    >Mark :o)
    >
    >
    >.
    >
    Edgardo Valdez Guest

  4. #4

    Default Re: Returning results where the match is not always there!!!

    Thank You,

    This is a great help :o)


    "Edgardo Valdez" <edgardovaldezhotmail.com> wrote in message
    news:042c01c33f4d$7b9d1670$a301280aphx.gbl...
    > Mark:
    >
    > You can do:
    >
    > select e-mail_address, [everything else you want]
    > from tbl1
    > left join tbl2
    > on tbl1.Account_Number = tbl2.Account_Number
    >
    > The matches that don't have e-mail addresses will return
    > null.
    >
    > >-----Original Message-----
    > >Hi All
    > >
    > >I have two tables that I need to match customer data on.
    > >The first table holds all of the customers billing info
    > >
    > >Title
    > >Name
    > >Address
    > >Account Number
    > >
    > >The second holds amongst other things the customer e-mail
    > address, dont ask
    > >why they are seperate it's not my database,
    > >
    > >Account number
    > >e-mail address
    > >etc
    > >etc
    > >
    > >In my where statment I match on the account number as
    > that is the only thing
    > >they have in common, "Where tbl1.Account_Number =
    > tbl2.Account_Number) but
    > >not everyone has an e-mail address.
    > >
    > >I only get results where there is a true match, how can
    > I say (select
    > >e-mail_address Where tbl1.Account_Number =
    > tbl2.Account_Number) but if there
    > >is no match still return the results from the first
    > table??
    > >
    > >
    > >Hope this makes sence
    > >
    > >Mark :o)
    > >
    > >
    > >.
    > >

    Sh0t2bts Guest

Similar Threads

  1. Recordset returning inconsistent results
    By RichardOMarketing in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: November 15th, 01:43 PM
  2. SQL Query Not Returning Correct Results
    By hml398 in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 30th, 04:05 AM
  3. Encrypt returning strange results
    By hoddle10 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: June 16th, 08:00 PM
  4. Verity - cfsearch not returning results on subsequentgets
    By Altawan in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 19th, 06:26 PM
  5. returning results in rows
    By Ken in forum ASP
    Replies: 2
    Last Post: September 17th, 02:15 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