SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 FULL JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

836 Posts

Posted - 04/17/2013 :  07:46:10  Show Profile  Reply with Quote
i have two tales ,details of orders:


OrdersA
OrderNo Part RefNo BonusFlag Amount
1         a   1      N        100


OrdersB
OrderNo Part RefNo BonusFlag Amount
1         a   1      N        100
1         a   1      Y         0


i want to join between orders that have bonus and thos that do not have.
if i do not have bonus rows,i also wnat to introduce this row
so i want to get 3 rows in my example:

but in my query i get just one,why?

SELECT *
OrdersA OrdersA
      FULL JOIN OrdersB OrdersB ON (OrdersA.RefNo =Ordersb.RefNo AND
OrdersA.Part =Ordersb.Part)
WHERE OrdersA.BonusFlag ='Y' AND OrdersB.BonusFlag ='Y'
UNION
SELECT *
OrdersA OrdersA
      FULL JOIN OrdersB OrdersB ON (OrdersA.RefNo =Ordersb.RefNo AND
OrdersA.Part =Ordersb.Part)
WHERE OrdersA.BonusFlag ='N' AND OrdersB.BonusFlag ='N'



Edited by - inbs on 04/17/2013 07:48:26

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/17/2013 :  08:14:20  Show Profile  Reply with Quote
--May be?

SELECT *
from @OrdersA A
FULL JOIN @OrdersB B ON (A.RefNo =B.RefNo AND A.Part =B.Part)
WHERE A.BonusFlag ='Y' 
UNION all
SELECT * from
@OrdersA A1
      FULL JOIN @OrdersB B1 ON (A1.RefNo =B1.RefNo AND A1.Part =B1.Part)
WHERE A1.BonusFlag ='N'


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000