2

I have a table of orders for particular products, and a table of products that are on sale. (It's not ideal database structure, but that's out of my control.) What I want to do is outer join the order table to the sale table via product number, but I don't want to include any particular data from the sale table, I just want a Y if the join exists or N if it doesn't in the output. Can anyone explain how I can do this in SQL?

Thanks in advance!

RSW
  • 21
  • 1
  • 3

3 Answers3

4

You should be able to use the CASE construct, and it would look something like this:

select
    order.foo,
    case
        when sale.argle is null then 'N'
        else 'Y'
    end
from order
left join sale on order.product_number = sale.product_number;
Hank Gay
  • 70,339
  • 36
  • 160
  • 222
  • Thanks for your quick response! I'll try that out now. – RSW May 13 '10 at 19:54
  • 1
    Just be sure that sale is a 1-to-1 relation for product_number. For instance, if the items on sale were broken down by region your customer my get invoiced for the same item four times (if the the item were on sale in 4 regions) – Scott Bailey May 13 '10 at 20:48
2

I nornally use NVL2 for this type of situation...

SELECT col_one
     , NVL2( col_one, 'Y', 'N' )   col_one_exists
     , col_two
     , NVL2( col_two, 'Y', 'N' )   col_two_exists
  FROM ( SELECT '12345'   col_one
              , NULL   col_two
           FROM dual
       )

Would return this:-

COL_ONE  COL_ONE_EXISTS  COL_TWO  COL_TWO_EXISTS
12345    Y                         N
PaulJ
  • 1,480
  • 9
  • 15
  • NVL2 is Oracle specific - the CASE statement is ANSI, so it's more portable. – OMG Ponies May 16 '10 at 17:39
  • 1
    The question was tagged for Oracle, so I answered accordingly. Database independence is not something I strive for. Quote: "You want to maximise the investment in your database. You want to develop the best software in the least amount of time against that database. The only way to do that is to fully exploit what the database has to offer." Effective Oracle By Design - Tom Kyte – PaulJ May 17 '10 at 07:34
0

Try (untested):

SELECT O.*, DECODE(NVL(p.product_num, 'X'), 'X', 'N', 'Y')
  FROM Orders AS o LEFT JOIN Products AS p ON o.Product_Num = p.Product_Num

The NVL will translate nulls in the 'p.product_num' to 'X', which will compare equal to 'X' in the DECODE, generating 'N'; non-null product numbers will be a number, not equal to 'X', and hence will generate a 'Y'.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • That's along the lines of what I was trying to do, but I think the first answer is more clear. Thanks! – RSW May 13 '10 at 20:01