February 26, 2015 at 5:57 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':
IDValue dueDate
1First NULL
2Second2014-01-02
3Third NULL
4FourthNULL
5Fifth NULL
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-01'
GO
Thank You
February 26, 2015 at 5:57 pm
Oops wrong date
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.dueDate='2014-01-02'
GO
February 26, 2015 at 6:06 pm
When you included the column from table2 in the WHERE clause, you converted your OUTER JOIN into an INNER JOIN.
This articles explains what happened: http://www.sqlservercentral.com/articles/T-SQL/93039/
And here's an example of how to mantain it as an OUTER JOIN.
SELECT t1.*,t2.dueDate
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
AND t2.dueDate='2014-01-02'
February 26, 2015 at 6:20 pm
Thank you very much .This is what i was looking for.Thank you for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply