December 7, 2015 at 2:04 pm
I have a simple UNION query (in all actuality it is a little more complicated than this, but this will work for simplification):
SELECT PRODUCTID, STATUS FROM Table1
UNION
SELECT PRODUCTID, STATUS FROM Table2
UNION
SELECT PRODUCTID, STATUS FROM Table3
The only problem I have is that the same PRODUCTID can exist in each Table, yet I only want to see the results for the highest Table that the PRODUCTID exists in. So, if the PRODUCTID exists in Table3, I only want to see the Status from Table3 for that ProductID and not from the other tables. Table3 always has higher priority then Table2, and Table2 has a higher priority then Table1. Also, all records in Table1 have the same status, all records in Table 2 have the same status, and all records in Table3 have the same status.
I'm not sure how to get the results I desire.
December 7, 2015 at 2:43 pm
You don't give sample data for us to use, so this is what I came up with...you may have to tweak it to work with your data.
**Ignore this code...I must have been sleeping...it really doesn't work **
--begin creating test tables and data
CREATE TABLE TABLE1
(productid INT,
status VARCHAR(20))
CREATE TABLE TABLE2
(productid INT,
status VARCHAR(20))
CREATE TABLE TABLE3
(productid INT,
status VARCHAR(20))
INSERT INTO table1
SELECT 1, 'open'
UNION
SELECT 2, 'open'
UNION
SELECT 3, 'open'
INSERT INTO table2
SELECT 1, 'in progress'
UNION
SELECT 2, 'in progress'
INSERT INTO table3
SELECT 1, 'closed'
--end creating test tables and data
DECLARE @ProductIDTable TABLE
(ProductID INT)
INSERT INTO @ProductIDTable (ProductID)
SELECT DISTINCT ProductID FROM Table1
UNION
SELECT DISTINCT ProductID FROM Table2
UNION
SELECT DISTINCT ProductID FROM Table3
DECLARE @productid INT
DECLARE @cntr INT
DECLARE @maxcntr INT
SET @cntr = 0
SET @maxcntr = (SELECT COUNT(ProductID) + 1 FROM @ProductIDTable)
WHILE @cntr < @maxcntr
BEGIN
SET @productid = (SELECT TOP 1 ProductID FROM @ProductIDTable)
IF @productid = (SELECT ProductID FROM Table3 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table3
END
ELSE IF @productid = (SELECT ProductID FROM Table2 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table2
END
ELSE IF @productid = (SELECT ProductID FROM Table1 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table1
END
SET @cntr = @cntr + 1
DELETE
FROM @ProductIDTable
WHERE productid = @productid
END
-SQLBill
December 7, 2015 at 3:06 pm
select productID, Status
into #tmpTable
from table3
insert #tmpTable
select productid,Status
from table2 t2
where t2.productID not in (select productID from #tmpTable)
insert #tmpTable
select productid,Status
from table1 t1
where t1.productID not in (select productID from #tmpTable)
December 7, 2015 at 3:09 pm
I came up with this that seems to work:
DECLARE @ProductIDTable TABLE
(ProductID INT)
INSERT INTO @ProductIDTable (ProductID)
SELECT DISTINCT ProductID FROM Table1
UNION
SELECT DISTINCT ProductID FROM Table2
UNION
SELECT DISTINCT ProductID FROM Table3
DECLARE @productid INT
DECLARE @cntr INT
DECLARE @maxcntr INT
SET @cntr = 0
SET @maxcntr = (SELECT COUNT(ProductID) + 1 FROM @ProductIDTable)
WHILE @cntr < @maxcntr
BEGIN
SET @productid = (SELECT TOP 1 ProductID FROM @ProductIDTable)
IF @productid = (SELECT ProductID FROM Table3 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table3
WHERE ProductID = @productid
END
ELSE IF @productid = (SELECT ProductID FROM Table2 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table2
WHERE ProductID = @productid
END
ELSE IF @productid = (SELECT ProductID FROM Table1 WHERE ProductID = @productid)
BEGIN
SELECT *
FROM Table1
WHERE ProductID = @productid
END
SET @cntr = @cntr + 1
DELETE
FROM @ProductIDTable
WHERE productid = @productid
END
-SQLBill
December 7, 2015 at 3:11 pm
I didn't see jshahan's post....that one seems more 'elegant'.
-SQLBill
December 7, 2015 at 3:12 pm
Using the fine sample data from SQLBill.
There is no need for looping here at all. RBAR will kill this kind of thing. We can use some window functions to make this a lot easier. There are other ways to accomplish this but here is one.
with ValuedResults as
(
select *, 3 as Importance from table3
union all
select *, 2 as Importance from table2
union all
select *, 1 as Importance from table1
)
, NumberedResults as
(
select productID
, status
, Importance
, ROW_NUMBER() over(partition by ProductID order by Importance desc) as RowNum
from ValuedResults
)
select productID
, status
from NumberedResults
where RowNum = 1
_______________________________________________________________
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/
December 7, 2015 at 4:50 pm
In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:
SELECT
COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,
COALESCE(t3.STATUS, t2.STATUS, t1.STATUS) AS STATUS
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t2.PRODUCTID = t1.PRODUCTID
FULL OUTER JOIN Table3 t3 ON t3.PRODUCTID IN ( t1.PRODUCTID, t2.PRODUCTID )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 8, 2015 at 7:23 am
ScottPletcher (12/7/2015)
In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:
SELECT
COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,
COALESCE(t3.STATUS, t2.STATUS, t1.STATUS) AS STATUS
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t2.PRODUCTID = t1.PRODUCTID
FULL OUTER JOIN Table3 t3 ON t3.PRODUCTID IN ( t1.PRODUCTID, t2.PRODUCTID )
That works too but from looking at the execution plan I don't think this would be my choice.
_______________________________________________________________
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/
December 8, 2015 at 8:59 am
Sean Lange (12/8/2015)
ScottPletcher (12/7/2015)
In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:
SELECT
COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,
COALESCE(t3.STATUS, t2.STATUS, t1.STATUS) AS STATUS
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t2.PRODUCTID = t1.PRODUCTID
FULL OUTER JOIN Table3 t3 ON t3.PRODUCTID IN ( t1.PRODUCTID, t2.PRODUCTID )
That works too but from looking at the execution plan I don't think this would be my choice.
Yeah, don't know why SQL is generating such a hideous plan. That's the natural way to code it, but not if the optimizer is that inept.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 8, 2015 at 9:07 am
ScottPletcher (12/8/2015)
Sean Lange (12/8/2015)
ScottPletcher (12/7/2015)
In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:
SELECT
COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,
COALESCE(t3.STATUS, t2.STATUS, t1.STATUS) AS STATUS
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t2.PRODUCTID = t1.PRODUCTID
FULL OUTER JOIN Table3 t3 ON t3.PRODUCTID IN ( t1.PRODUCTID, t2.PRODUCTID )
That works too but from looking at the execution plan I don't think this would be my choice.
Yeah, don't know why SQL is generating such a hideous plan. That's the natural way to code it, but not if the optimizer is that inept.
I thought the same thing. I liked the OUTER JOIN as it seems much simpler and cleaner than my attempt but the optimizer seems to be confused by that. Weird.
_______________________________________________________________
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/
December 8, 2015 at 9:19 am
Now this is why I like these forums.....
Someone posts code that works but might not be the best way (me) and then others follow-up with their suggestions. No one says anything like "your code is a piece of crap", just "here's another way and here's why this might be better". No put-downs or insults involved. Let's everyone learn without anyone's feelings being hurt or belittled. Of course, there are a few who don't act this way, but I have found the majority do.
This is what all forums should be like.
-SQLBill
December 8, 2015 at 9:32 am
SQLBill (12/8/2015)
Now this is why I like these forums.....Someone posts code that works but might not be the best way (me) and then others follow-up with their suggestions. No one says anything like "your code is a piece of crap", just "here's another way and here's why this might be better". No put-downs or insults involved. Let's everyone learn without anyone's feelings being hurt or belittled. Of course, there are a few who don't act this way, but I have found the majority do.
This is what all forums should be like.
-SQLBill
Agreed!!! The attempt by nearly everyone on these forums is to always keep it professional. That by extension means the typical insults and personal attacks have no place around here. We all need reminders once in awhile to keep that going and positive feedback is an awesome reminder!!!
_______________________________________________________________
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/
December 8, 2015 at 2:35 pm
If the tables are indexed on id, you just have to force SQL to use the obvious MERGE join it should have been using anyway(!) 😀 :
DROP TABLE #TABLE1
DROP TABLE #TABLE2
DROP TABLE #TABLE3
CREATE TABLE #TABLE1
(productid INT,
status VARCHAR(20))
CREATE CLUSTERED INDEX TABLE1__CL ON #TABLE1 ( productid );
CREATE TABLE #TABLE2
(productid INT,
status VARCHAR(20))
CREATE CLUSTERED INDEX TABLE2__CL ON #TABLE2 ( productid );
CREATE TABLE #TABLE3
(productid INT,
status VARCHAR(20))
CREATE CLUSTERED INDEX TABLE3__CL ON #TABLE3 ( productid );
INSERT INTO #table1
SELECT 1, 'open'
UNION
SELECT 2, 'open'
UNION
SELECT 3, 'open'
INSERT INTO #table2
SELECT 1, 'in progress'
UNION
SELECT 2, 'in progress'
UNION
SELECT 5, 'in progress'
INSERT INTO #table3
SELECT 1, 'closed'
UNION
SELECT 6, 'closed'
SELECT
COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,
COALESCE(t3.STATUS, t2.STATUS, t1.STATUS) AS STATUS
FROM #Table1 t1
FULL OUTER MERGE JOIN #Table2 t2 ON t2.PRODUCTID = t1.PRODUCTID
FULL OUTER MERGE JOIN #Table3 t3 ON t3.PRODUCTID IN ( t1.PRODUCTID, t2.PRODUCTID )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 8, 2015 at 7:33 pm
SQLBill (12/8/2015)
Now this is why I like these forums.....Someone posts code that works but might not be the best way (me) and then others follow-up with their suggestions. No one says anything like "your code is a piece of crap", just "here's another way and here's why this might be better". No put-downs or insults involved. Let's everyone learn without anyone's feelings being hurt or belittled. Of course, there are a few who don't act this way, but I have found the majority do.
This is what all forums should be like.
-SQLBill
It's amazing.... the professionalism of this forum and all of the great suggestions I receive when asking a question. Even the "not the best way" solutions you mention help me learn something new. I love to see how many different ways people come up with to solve the problem.
Thank you all for the suggested solutions. My next objective is to be able to understand execution plans/optimizing my queries. That way I could compare and figure out which works the best for my data. Any recommended reads for query optimization and understanding execution plans?
December 9, 2015 at 9:23 am
skilly2 (12/8/2015)
SQLBill (12/8/2015)
Now this is why I like these forums.....Someone posts code that works but might not be the best way (me) and then others follow-up with their suggestions. No one says anything like "your code is a piece of crap", just "here's another way and here's why this might be better". No put-downs or insults involved. Let's everyone learn without anyone's feelings being hurt or belittled. Of course, there are a few who don't act this way, but I have found the majority do.
This is what all forums should be like.
-SQLBill
It's amazing.... the professionalism of this forum and all of the great suggestions I receive when asking a question. Even the "not the best way" solutions you mention help me learn something new. I love to see how many different ways people come up with to solve the problem.
Thank you all for the suggested solutions. My next objective is to be able to understand execution plans/optimizing my queries. That way I could compare and figure out which works the best for my data. Any recommended reads for query optimization and understanding execution plans?
Here is a great book on the topic. There is a free download but the cost of the book is not that much. https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2
_______________________________________________________________
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply