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.
| Author |
Topic |
|
Petronas
Posting Yak Master
134 Posts |
 Posted - 2008-11-21 : 15:27:51
|
| Hi ,I have two select statements. One to get orders_received and one for Orders_cancel. select order_id as Orders_Receivedfrom orders as o where o.cancel_date is nulland o.marketing in (34109,34111)select order_id as Orders_cancelledfrom orders as o where o.cancel_date is not nulland o.marketing in (34109,34111)I want the result to be shown asOrders_Received Orders_cancelled3456676 5676686787847 893804How do I go about joining these two statement to get my outputThanks,Petronas |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-11-21 : 15:44:29
|
| What happens if there are more orders received than cancelled, i.e. 4 rows vs. 5 rows? |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-21 : 15:46:39
|
| Why would you want to join order id ?? What basis ?? |
|
|
|
Petronas
Posting Yak Master
134 Posts |
Posted - 2008-11-21 : 16:10:40
|
| The requirement is to provide the order_received_date as the parameter for the orders_received.and the cancel_date as the parameter for the orders_cancel and union the both. I know the row numbers are not same. Any ideas of how this can be done? Thanks for all your help,Petronas |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-21 : 16:28:26
|
quote:
Originally posted by Petronas
The requirement is to provide the order_received_date as the parameter for the orders_received.and the cancel_date as the parameter for the orders_cancel and union the both. I know the row numbers are not same. Any ideas of how this can be done? Thanks for all your help,Petronas
quote:
Originally posted by Petronas
The requirement is to provide the order_received_date as the parameter for the orders_received.and the cancel_date as the parameter for the orders_cancel and union the both. I know the row numbers are not same. Any ideas of how this can be done? Thanks for all your help,Petronas
I'm sorry. I am not understanding it correct probably. If you want to union them both, then why join it ?? Or is it just for display/reporting purpose?If its for reporting, rather have front end handle it. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-21 : 18:09:34
|
| I know this may not be correct but Here is my shot:select (case when cancel_date is null then Order_id else 0 end)as Order_received,(case when cancel_date is not null then Order_id else 0 end)as Ordered_Cancelledfrom OrdersWhere marketing in (34109,34111) |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-11-22 : 15:32:27
|
| This is probably easy but you need to explain better. What is that example input data for the example results you gave, and what are the results for the two individual queries you gave? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-23 : 01:48:37
|
quote:
Originally posted by Petronas
The requirement is to provide the order_received_date as the parameter for the orders_received.and the cancel_date as the parameter for the orders_cancel and union the both. I know the row numbers are not same. Any ideas of how this can be done? Thanks for all your help,Petronas
show some sample data from tables and explain what you want as output. that should make matters easyOne possible solution can be along the lines of what Sodeep posted, but without seeing data we cant confirm. Try to provide info in below formathttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|
|
Joining two select statements