Query for Joining Two Tables With Possible Multiple Mapping
- by Dharmendra Mohapatra
First_table
srno wono Actual_Start_Date Actual_End_Date
1 31 2012-06-02 2012-06-05
2 32 2012-06-05 2012-06-22
3 33 2012-06-11 2012-06-23
4 34 2012-06-23 2012-06-30
5 A-2 2012-06-24 2012-06-25
6 BU 2012-06-24 2012-06-26
7 40 2012-06-25 2012-06-27
second_table
srno wono Base_start_date Base_end_date uploadhistoryid
1 31 2012-06-05 2012-06-05 1
2 32 2012-06-11 2012-06-12 2
3 32 2012-06-15 2012-06-17 3
4 32 2012-06-18 2012-06-20 4
5 33 2012-06-22 2012-06-25 5
5 33 2012-06-23 2012-06-25 5
Result Required
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Reports_Subanalysis]
(
@WONo VARCHAR(20)
)
AS
BEGIN
SELECT
'SAT' AS stage,
s.Base_start_date AS start_date,
s.Base_end_date AS end_date,
f.Actual_Start_Date AS Actual_Start_Date,
f.Actual_end_Date AS Actual_End_Date
FROM First_table f,
second_table B
WHERE A.wOno=B.nOno
AND f.uploadhistoryid in (SELECT min(uploadhistoryid) FROM second_table C WHERE f.wono = C.wono)
AND b.wono=@WONo
END
when I pass '32'
Result
stage start_date end_date Actual_Start_Date Actual_End_Date
SAT 2012-06-11 2012-06-12 2012-06-05 2012-06-05
how Can I get the result like this when I pass non matching value like 'BU'
stage start_date end_date Actual_Start_Date Actual_End_Date
SAT NULL NULL 2012-06-24 2012-06-26
What modification do I need in my routine?
