July 27, 2015 at 1:26 pm
I have below two tables
K1:
IDdate I1
11-May-147
11-May-144
11-May-145
1Jun-142
1Oct-141
2Jan-1310
K2:
IDdate T1
11-May-14300
1Jun-14400
1Jan-15100
1Apr-15200
1Oct-131000
3Jan-15600
I want a result table as:
IDdate T1I1
11-May-143007
11-May-143004
11-May-143005
1Jun-144002
1Jan-151000
1Apr-152000
1Oct-1310000
1Jun-1402
1Oct-1401
2Jan-13010
3Jan-156000
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:
IDdate I1
11-May-147
11-May-144
11-May-145
1Jun-142
1Oct-141
2Jan-1310
K2:
IDdate T1
11-May-14300
1Jun-14400
1Jan-15100
1Apr-15200
1Oct-131000
3Jan-15600
I want a result table as:
IDdate T1I1
11-May-143007
11-May-143004
11-May-143005
1Jun-144002
1Jan-151000
1Apr-152000
1Oct-1310000
1Jun-1402
1Oct-1401
2Jan-13010
3Jan-156000
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 1AS 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 1AS 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 1AS 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 1AS 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 1AS 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 1AS 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