Clarification on Left 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 2005 Forums

Transact-SQL (2005)

Clarification on Left Join.

Author

Topic

anandchinnappan
Starting Member

7 Posts

Posted - 2010-03-28 : 15:14:20

Dear Experts,

I have two views.

1. PlannedOrderStatus
2. ActualOrderStatus.

Both of them have same structure.

The columns are

OrderRef.......Process.......Qty

The PlannedOrderStatus view holds information planned by Merchandiser.
The ActualOrderStatus has information on Actual happening.

I need to combine both the views, So that I could have Planned Qty and Actual Quantity next to each other. To accomplish this, I used Left join method. Some times planned process might not have started yet in actual. so in order to have all planning as well as actual completion of process I used left join. Its not working. When I used to statement,

create view OrderStatus as
select distinct t0.Process , max(t0.ORefNo) ONo,MAX(t0.Qty) Planned,MAX(t1.qty) Actual from PlannedOrderStatus t0
left join ActualOrderStatus t1 on t0.OREFNO=t1.OREFNO Group by t0.PROCESS,t0.OREFNO

It fetches a qty from RHS View and combines with every row of LHS View.

When

create view OrderStatus as
select distinct t0.Process , max(t0.ORefNo) ONo,MAX(t0.Qty) Planned,MAX(t1.qty) Actual from PlannedOrderStatus t0
left join ActualOrderStatus t1 on t0.process=t1.process Group by t0.PROCESS,t0.OREFNO

is used, It picks up random values from RHS and combines, those values no way related to the order in question.

Please help me to solve this.

Best Regards
Anand

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-28 : 19:14:37

Are the OrderRef column in both queries returning a distinct list?

Just a guess here, but what happens when you use:

create view OrderStatus as
select distinct t0.Process , max(t0.ORefNo) ONo,MAX(t0.Qty) Planned,MAX(t1.qty) Actual from PlannedOrderStatus t0
left join ActualOrderStatus t1 on t0.OREFNO=t1.OREFNO
and t0.process=t1.process
Group by t0.PROCESS,t0.OREFNO

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 00:33:43

will you be having multiple records per t0.PROCESS,t0.OREFNO group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anandchinnappan
Starting Member

7 Posts

Posted - 2010-03-29 : 13:34:34

Thanks experts, my problem solved.

Regards
Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 13:36:09

quote:
Originally posted by anandchinnappan

Thanks experts, my problem solved.

Regards
Anand

How?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources