February 26, 2015 at 6:05 pm
Hi all,
I need to create report and need help.Here are 2 tables with some data.
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, dueDate DATETIME)
INSERT INTO Table2 (ID, dueDate)
SELECT 1,'2014-01-01'
UNION ALL
SELECT 2,'2014-01-02'
UNION ALL
SELECT 3,'2014-01-03'
UNION ALL
SELECT 6,'2014-01-04'
UNION ALL
SELECT 7,'2014-01-05'
UNION ALL
SELECT 8,'2014-01-06'
GO
I need all id's from table1 and dueDate from table2 for specific dueDate.If table2 dueDate does not match my dueDate then show 'null' or any 0.if matches then show dueDate.This is how it should look is i say dueDate='2014-01-02':
IDValuedueDate
1FirstNULL
2Second2014-01-02
3ThirdNULL
4FourthNULL
5FifthNULL
this is script that came up with which is not what i want
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.dueDate='2014-01-02'
GO
Thank You
February 26, 2015 at 7:37 pm
Thanks for the DDL... that' helps a lot. But could you post the result that you are looking for? Then someone can figure out how to do the query.
February 26, 2015 at 10:58 pm
Several ways of doing this, here is one suggestion using a CTE. The important thing here is to pre-filter the second set because any filtering after joining the sets will effectively turn the left outer join / cross/outer apply into an inner join.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.table1') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(ID INT, Value VARCHAR(10))
INSERT INTO dbo.Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
IF OBJECT_ID(N'dbo.table2') IS NOT NULL DROP TABLE dbo.Table2;
CREATE TABLE dbo.Table2
(ID INT, dueDate DATETIME)
INSERT INTO dbo.Table2 (ID, dueDate)
SELECT 1,'2014-01-01'
UNION ALL
SELECT 2,'2014-01-02'
UNION ALL
SELECT 3,'2014-01-03'
UNION ALL
SELECT 6,'2014-01-04'
UNION ALL
SELECT 7,'2014-01-05'
UNION ALL
SELECT 8,'2014-01-06'
GO
DECLARE @dueDate DATETIME = '2014-01-02';
;WITH BASE_DATA AS
(
SELECT
T2.ID
,T2.dueDate
FROM dbo.Table2 T2
WHERE T2.dueDate = @dueDate
)
SELECT
T1.ID
,T1.Value
,T2.dueDate
FROM dbo.Table1 T1
LEFT OUTER JOIN BASE_DATA T2
ON T1.ID = T2.ID;
Results
ID Value dueDate
----------- ---------- -----------------------
1 First NULL
2 Second 2014-01-02 00:00:00.000
3 Third NULL
4 Fourth NULL
5 Fifth NULL
February 27, 2015 at 12:04 am
Barcelona10 (2/26/2015)
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.dueDate='2014-01-02'
GO
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
AND t2.dueDate='2014-01-02'
February 27, 2015 at 1:31 am
serg-52 (2/27/2015)
Barcelona10 (2/26/2015)
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.dueDate='2014-01-02'
GO
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
AND t2.dueDate='2014-01-02'
Quick word of caution, although a simple solution, it can lead to a rather inefficient execution plan compared to the ones where the set is pre-filtered, even for this small sample, the cost ratio is 1:4
😎
February 27, 2015 at 3:22 am
Already answered in dupe post
http://www.sqlservercentral.com/Forums/Topic1664045-391-1.aspx
Far away is close at hand in the images of elsewhere.
Anon.
February 27, 2015 at 9:48 am
Thank You ALL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply