Results 1 to 6 of 6

Thread: Help

  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: Help

    Hi,

    I'm no ms sql guru, but I'm going to do my best to try and explain what I'm trying to do. Here is what I have now:

    Code:
    SELECT  [Subscriber Data].AcctLineCode, [Subscriber Data].AcctNum, [Subscriber Data].Name, [Subscriber Data].Address, [Subscriber Data].Zip
    FROM  [Subscriber Data] INNER JOIN
                [Phone Numbers] ON [Subscriber Data].AccountID = [Phone Numbers].AccountID
    WHERE  ([Subscriber Data].AcctLineCode = '40') AND ([Phone Numbers].Number LIKE '%7776666%')
    What I have works fine, however - I would like to include an additional row of data returned that is located in [Phone Numbers].Number WHERE the [Phone Numbers].Description is LIKE '%prem%'.

    My WHERE criteria seems to conflict with this and I'm not sure what to do in this case. It's like I need an additional SELECT statement inside my select statement, or something.

    I'm grateful for any help!

    Thanks,

    --
    Kevin

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Consider this a SWAG, not code ready to use:
    Code:
    SELECT  [Subscriber Data].AcctLineCode, [Subscriber Data].AcctNum
    ,  [Subscriber Data].Name, [Subscriber Data].Address
    ,  [Subscriber Data].Zip
    ,  a.Thingie, b.Mabob
       FROM  [Subscriber Data] 
       LEFT JOIN [Phone Numbers] AS a
          ON [Subscriber Data].AccountID = a.AccountID
          AND (a.Number LIKE '%7776666%')
       LEFT JOIN [Phone Numbers] AS b
          ON [Subscriber Data].AccountID = b.AccountID
          AND (a.Description LIKE '%prem%')
       WHERE  ([Subscriber Data].AcctLineCode = '40')
    The trick is that there are two LEFT JOIN operations on the [Phone Numbers] table, named a and b... Each of them can and probably will have data from different rows in the [Phone Number] table.

    Depending on what your schema does with the [Phone Number] table, my logic might be kind of "interesting" so pay close/critical attention to it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    Sweet. I will ding around with this and report back.

    Thanks!

    --
    Kevin

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    Worked like a charm.

    Here is the final query I ended up using for anyone that might have a similar question in the future.

    Code:
    SELECT [Subscriber Data].AcctLineCode, [Subscriber Data].AcctNum
      , [Subscriber Data].Name, [Subscriber Data].Address
      , [Subscriber Data].Zip, b.Number AS 'Prem'
    
    FROM [Subscriber Data] LEFT OUTER JOIN
              [Phone Numbers] a ON [Subscriber Data].AccountID 
                = a.AccountID LEFT OUTER JOIN
              [Phone Numbers] b ON [Subscriber Data].AccountID 
                = b.AccountID AND b.Description LIKE '%prem%'
    
    WHERE ([Subscriber Data].AcctLineCode = '40') 
      AND (a.Number LIKE '%7776666%')
    
    ORDER BY [Subscriber Data].AcctNum
    Let me know if there is anything that could have been improved. I seem to be getting the results that I wanted.

    Thanks again for your help!

    --
    Kevin

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without understanding more about your schema, I can't be certain one way or the other. This looks good to me, and if you think it produces the correct results then I'd give it a relatively solid stamp of approval.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2012
    Posts
    4
    Awesome. Thanks again for your help Pat.

    --
    Kevin

Posting Permissions

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