July 5, 2011 at 2:52 am
Hi,
I need a help on the below:
Have two tables. One is master and next is details.
I need a result which satisfies below conditions
All the rows from master even if rows are not in details.
If rows is present then Date column should be NULL or Greater than Current Date.
I have tried using left join, but not getting the desired result.
Please help. Thanks in advance.
sample structure is as below:
Master:
ID Name
1 DWK
2 MWPL
3 RK
Details:
* n - may be any integer
ID Name Date MName
1 STM1 NULL DWK
2 STM2 Current Date-n DWK
3 STM3 CurrentDate+n DWK
4 STM4 NULL RK
5 STM5 Current Date-n RK
Now my result should be as below:
ID Name Date
1 STM1 NULL
3 STM3 CurrentDate+n
4 STM4 CurrentDate+n
July 5, 2011 at 3:34 am
If I understand your question correctly, this could be an answer:
-- create temp tables
DECLARE @master TABLE (
ID int NOT NULL PRIMARY KEY,
Name varchar(4)
)
DECLARE @Details TABLE (
ID int NOT NULL,
Name char(4),
[Date] datetime,
MName varchar(4)
)
-- insert sample data
INSERT INTO @master
SELECT 1, 'DWK'
UNION ALL SELECT 2, 'MWPL'
UNION ALL SELECT 3, 'RK'
INSERT INTO @Details
SELECT 1, 'STM1', NULL, 'DWK'
UNION ALL SELECT 2, 'STM2', CONVERT(char(8), DATEADD(day, -3, GETDATE()), 112), 'DWK'
UNION ALL SELECT 3, 'STM3', CONVERT(char(8), DATEADD(day, +3, GETDATE()), 112), 'DWK'
UNION ALL SELECT 4, 'STM4', NULL, 'RK'
UNION ALL SELECT 5, 'STM5', CONVERT(char(8), DATEADD(day, -3, GETDATE()), 112), 'RK'
-- select out results
SELECT D.ID, D.Name, D.[Date]
FROM @master AS M
INNER JOIN @Details AS D
ON M.Name = D.MName
WHERE D.[Date] IS NULL
OR D.[Date] >= CONVERT(char(8), GETDATE(), 112)
Based on your sample data, the expected results for the third row (ID = 4) should not include a date, but the NULL value.
Also, since you are not selecting fields from the master table, an INNER JOIN is enough.
Did I miss something?
-- Gianluca Sartori
July 5, 2011 at 3:43 am
Thanks for your quick response. But
Sorry it is actuall my mistake. I given the sample output wrongly.
The output should be: (All the rows from master even if rows are not in details.)
(If rows is present then Date column should be NULL or Greater than Current Date.)
M.ID M.Name D.Name D.Date
1 DWK STM1 NULL
1 DWK STM3 CurrentDate+n
2 MWPL NULL NULL
3 RK STM4 NULL
Please help.
July 5, 2011 at 3:45 am
In this case inner join will not give all the rows from master table.
We should use left join only. But if left join is used, date clause in not giving the exact result.
July 5, 2011 at 3:56 am
mageshh11 (7/5/2011)
Thanks for your quick response. ButSorry it is actuall my mistake. I given the sample output wrongly.
The output should be: (All the rows from master even if rows are not in details.)
(If rows is present then Date column should be NULL or Greater than Current Date.)
M.ID M.Name D.Name D.Date
1 DWK STM1 NULL
1 DWK STM3 CurrentDate+n
2 MWPL NULL NULL
3 RK STM4 NULL
Please help.
With these expected results, the code can be changed this way:
SELECT M.ID, M.Name, D.[Date]
FROM @master AS M
LEFT JOIN @Details AS D
ON M.Name = D.MName
WHERE D.[Date] IS NULL
OR D.[Date] >= CONVERT(char(8), GETDATE(), 112)
-- Gianluca Sartori
July 5, 2011 at 7:29 am
Thanks for the quick response.
That solved my issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply