July 27, 2015 at 1:26 pm
I have below two tables
K1:
ID date I1
1 1-May-14 7
1 1-May-14 4
1 1-May-14 5
1 Jun-14 2
1 Oct-14 1
2 Jan-13 10
K2:
ID date T1
1 1-May-14 300
1 Jun-14 400
1 Jan-15 100
1 Apr-15 200
1 Oct-13 1000
3 Jan-15 600
I want a result table as:
ID date T1 I1
1 1-May-14 300 7
1 1-May-14 300 4
1 1-May-14 300 5
1 Jun-14 400 2
1 Jan-15 100 0
1 Apr-15 200 0
1 Oct-13 1000 0
1 Jun-14 0 2
1 Oct-14 0 1
2 Jan-13 0 10
3 Jan-15 600 0
I.e all the data from both tables based on Id and date. if data for perticular id doesn't exist on any period, then it should set to 0 for that column.
What ever join i am using, it is giving cartesian product which i dont want.
Thanks
July 27, 2015 at 1:47 pm
This should give you what you're looking for...
SELECT ID, [Date],0 AS T1, I1 FROM K1
UNION ALL
SELECT ID, [Date], T1, 0 AS I1 FROM K2
ORDER BY ID, [Date]
July 27, 2015 at 1:47 pm
sqlinterset (7/27/2015)
I have below two tablesK1:
ID date I1
1 1-May-14 7
1 1-May-14 4
1 1-May-14 5
1 Jun-14 2
1 Oct-14 1
2 Jan-13 10
K2:
ID date T1
1 1-May-14 300
1 Jun-14 400
1 Jan-15 100
1 Apr-15 200
1 Oct-13 1000
3 Jan-15 600
I want a result table as:
ID date T1 I1
1 1-May-14 300 7
1 1-May-14 300 4
1 1-May-14 300 5
1 Jun-14 400 2
1 Jan-15 100 0
1 Apr-15 200 0
1 Oct-13 1000 0
1 Jun-14 0 2
1 Oct-14 0 1
2 Jan-13 0 10
3 Jan-15 600 0
I.e all the data from both tables based on Id and date. if data for perticular id doesn't exist on any period, then it should set to 0 for that column.
What ever join i am using, it is giving cartesian product which i dont want.
Thanks
This would be a lot easier if you had posted ddl and sample data. It seems you need a full outer join here because you want to find rows from either side regardless if they match. Then you will need to use coalesce/isnull to use the default 0 when there is no match. You should take a look at this article which does an awesome job explaining the different types of joins. http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2015 at 2:05 pm
Jason A. Long (7/27/2015)
This should give you what you're looking for...
SELECT ID, [Date],0 AS T1, I1 FROM K1
UNION ALL
SELECT ID, [Date], T1, 0 AS I1 FROM K2
ORDER BY ID, [Date]
This doesn't quite meet the requirements the OP is looking for. In their desired output they have values from both tables in a given row. And when the join does not find a match it returns 0 instead of the value from the other table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2015 at 2:20 pm
Table1:
SELECT 1 AS ID,'May-2014' AS date,7 AS I1
UNION ALL SELECT 1 ,'May-2014' ,4
UNION ALL SELECT 1 ,'May-2014' ,5
UNION ALL SELECT 1 ,'Jun- 2014' ,2
UNION ALL SELECT 1 ,'Oct-2014' ,1
UNION ALL SELECT 2 ,'Jan-2013' ,10;
Table2:
SELECT 1 AS ID,'May-2014' AS date,300 AS T1
UNION ALL SELECT 1 ,'Jun-2014' ,400
UNION ALL SELECT 1 ,'Jan-2015' ,100
UNION ALL SELECT 1 ,'Apr-2015' ,200
UNION ALL SELECT 1 ,'Oct-2013' ,1000
UNION ALL SELECT 3 ,'Jan-2015' ,600;
Resulted table:
SELECT 1 AS ID,'May-2014' AS date,300 AS T1, 7 as I1
UNION ALL SELECT 1 ,'May-2014' ,300,4
UNION ALL SELECT 1 ,'May-2014' ,300,5
UNION ALL SELECT 1 ,'Jun-2014' ,400,2
UNION ALL SELECT 1 ,'Jan-2015' ,100,0
UNION ALL SELECT 1 ,'Apr-2015' ,200,0
UNION ALL SELECT 1 ,'Oct-2013' ,1000,0
UNION ALL SELECT 1 ,'Jun-2014' ,0,2
UNION ALL SELECT 1 ,'Oct-2014' ,0,1
UNION ALL SELECT 2 ,'Jan-2013' ,0,10
UNION ALL SELECT 3 ,'Jan-2015' ,600,0;
I hope this will help
July 27, 2015 at 2:20 pm
You are right.
July 27, 2015 at 2:39 pm
sqlinterset (7/27/2015)
Table1:SELECT 1 AS ID,'May-2014' AS date,7 AS I1
UNION ALL SELECT 1 ,'May-2014' ,4
UNION ALL SELECT 1 ,'May-2014' ,5
UNION ALL SELECT 1 ,'Jun- 2014' ,2
UNION ALL SELECT 1 ,'Oct-2014' ,1
UNION ALL SELECT 2 ,'Jan-2013' ,10;
Table2:
SELECT 1 AS ID,'May-2014' AS date,300 AS T1
UNION ALL SELECT 1 ,'Jun-2014' ,400
UNION ALL SELECT 1 ,'Jan-2015' ,100
UNION ALL SELECT 1 ,'Apr-2015' ,200
UNION ALL SELECT 1 ,'Oct-2013' ,1000
UNION ALL SELECT 3 ,'Jan-2015' ,600;
Resulted table:
SELECT 1 AS ID,'May-2014' AS date,300 AS T1, 7 as I1
UNION ALL SELECT 1 ,'May-2014' ,300,4
UNION ALL SELECT 1 ,'May-2014' ,300,5
UNION ALL SELECT 1 ,'Jun-2014' ,400,2
UNION ALL SELECT 1 ,'Jan-2015' ,100,0
UNION ALL SELECT 1 ,'Apr-2015' ,200,0
UNION ALL SELECT 1 ,'Oct-2013' ,1000,0
UNION ALL SELECT 1 ,'Jun-2014' ,0,2
UNION ALL SELECT 1 ,'Oct-2014' ,0,1
UNION ALL SELECT 2 ,'Jan-2013' ,0,10
UNION ALL SELECT 3 ,'Jan-2015' ,600,0;
I hope this will help
That is close. Please see my post for how you could have made this easier. The way I did this lets somebody dive in a start immediately.
This should produce the desired output. If you look at the visual representation I posted earlier you should be able to see how this is working.
with table1 as
(
SELECT 1 AS ID,'May-2014' AS date,7 AS I1
UNION ALL SELECT 1 ,'May-2014' ,4
UNION ALL SELECT 1 ,'May-2014' ,5
UNION ALL SELECT 1 ,'Jun- 2014' ,2
UNION ALL SELECT 1 ,'Oct-2014' ,1
UNION ALL SELECT 2 ,'Jan-2013' ,10
)
, Table2 as
(
SELECT 1 AS ID,'May-2014' AS date,300 AS T1
UNION ALL SELECT 1 ,'Jun-2014' ,400
UNION ALL SELECT 1 ,'Jan-2015' ,100
UNION ALL SELECT 1 ,'Apr-2015' ,200
UNION ALL SELECT 1 ,'Oct-2013' ,1000
UNION ALL SELECT 3 ,'Jan-2015' ,600
)
select ISNULL(t.ID, w.ID) as ID
, ISNULL(t.date, w.date)as date
, ISNULL(w.T1, 0) as T1
, ISNULL(t.I1, 0) as I1
from table1 t
full outer join table2 w on w.ID = t.ID and w.date = t.date
order by date
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2015 at 4:41 pm
Sean Lange (7/27/2015)
Jason A. Long (7/27/2015)
This should give you what you're looking for...
SELECT ID, [Date],0 AS T1, I1 FROM K1
UNION ALL
SELECT ID, [Date], T1, 0 AS I1 FROM K2
ORDER BY ID, [Date]
This doesn't quite meet the requirements the OP is looking for. In their desired output they have values from both tables in a given row. And when the join does not find a match it returns 0 instead of the value from the other table.
Good catch... I must be going blind... :blink:
July 27, 2015 at 6:28 pm
When i do it... it takes all values from left table and only those values from right table where id and date is matching.
July 28, 2015 at 2:27 am
Use full outer join
July 28, 2015 at 7:12 am
sony.francis 69835 (7/28/2015)
Use full outer join
You mean like the code I posted?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply