FULL JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

FULL JOIN

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-04-17 : 07:46:10

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'

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 08:14:20

--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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources