t-sql left outer join

  • In t-sql 2208 r2, I have sql that looks like the following:

    select a.custname, i.item, i.amount, so.specialitem, so.specialamount

    from customer a

    inner join inventory i on a.custid = i.custid

    left outer join on specialorder so on so.custid = so.custid

    The problem is with the left outer join on the specialorder table.

    Only about 50% of the time is there a special order. In rare cases,

    there are 2 or more specialorder items that a customer picks.

    My problem is that the user only wants one spcialorder item to be reported on.

    The user does not care which specialorder item is appearing, they just one one

    item selected.

    Thus can you tell me how to change the sql listed above so that I can do

    what my user wants to see?

  • Without knowing a bit more, this is the best I can do to give you a starting place. This puts a "row number" on each special order, then joins that to the other tables where [row] = 1. Make sure you understand the code before you use it!

    WITH specialOrders () {

    SELECT so.CustID, so.SpecialItem, sp.SpecialAmount,

    row = ROW_NUMBER() OVER (ORDER BY so.CustID)

    FROM dbo.SpecialOrder so

    }

    select a.custname, i.item, i.amount, so.specialitem, so.specialamount

    from dbo.customer a inner join

    dbo.inventory i on a.custid = i.custid left outer join

    (SELECT * FROM specialOrders WHERE [Row] = 1) AS so on so.custid = so.custid

    wendy elizabeth (5/20/2014)


    In t-sql 2208 r2, I have sql that looks like the following:

    select a.custname, i.item, i.amount, so.specialitem, so.specialamount

    from customer a

    inner join inventory i on a.custid = i.custid

    left outer join on specialorder so on so.custid = so.custid

    The problem is with the left outer join on the specialorder table.

    Only about 50% of the time is there a special order. In rare cases,

    there are 2 or more specialorder items that a customer picks.

    My problem is that the user only wants one spcialorder item to be reported on.

    The user does not care which specialorder item is appearing, they just one one

    item selected.

    Thus can you tell me how to change the sql listed above so that I can do

    what my user wants to see?

  • You don't even need the ROW_NUMBER if you don't care about the order. You could use the APPLY operator.

    SELECT a.custname, i.item, i.amount, s.specialitem, s.specialamount

    FROM customer a

    INNER JOIN inventory i on a.custid = i.custid

    OUTER APPLY (SELECT TOP 1 so.specialitem, so.specialamount

    FROM specialorder so

    WHERE a.custid = so.custid) s

    EDIT: I forgot to mention that your code had a wrong JOIN condition that would cause a CROSS JOIN (so.custid = so.custid).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • what does the 'outer apply' do in the sql you just listed?

  • I could try to explain it, but Paul White already did a great job with a series of two articles that explain how APPLY works and how it is a great tool you should learn.

    Understanding and Using APPLY (Part 1)[/url]

    Understanding and Using APPLY (Part 2)[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply