May 22, 2017 at 4:21 am
I have two tables.
I am looking for an approach where
1)I need to display the data from Table1 if table 2 count is zero.
2)I need to display the data from Table2 if table1 Count is zero
3) Should display the Common if both has the Data.
Sample ddl below.
Create Table #temp1
(
id int not null,
Name Varchar(10)
)
Create Table #temp2
(
id int not null,
Name Varchar(10)
)
Insert into #temp1
select 1, 'SQL'
union all
select 2, 'SSIS'
union all
select 3, 'SSRS'
Insert into #temp2
select 1, 'SSAS'
union all
select 2, 'SSIS'
union all
select 3, 'postgre'
May 22, 2017 at 4:33 am
SELECT
id
, Name
, 'temp1' AS SourceTable
FROM #temp1
UNION ALL
SELECT
id
, Name
, 'temp2'
FROM #temp2
John
May 22, 2017 at 6:33 am
Something like this:
IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)
SELECT * FROM #temp1
INTERSECT
SELECT * FROM #temp2;
ELSE
SELECT * FROM #temp1
UNION ALL
SELECT * FROM #temp2;
May 22, 2017 at 12:42 pm
Luis Cazares - Monday, May 22, 2017 6:33 AMSomething like this:
IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)
SELECT * FROM #temp1
INTERSECT
SELECT * FROM #temp2;
ELSE
SELECT * FROM #temp1
UNION ALL
SELECT * FROM #temp2;
RACE CONDITION!
lol well not if using local temp tables so never mind 🙂
May 22, 2017 at 1:41 pm
UNION ALL will cause each Name to be repeated if it appears in both tables.
Are the ID values significant? If by "display the Common" you mean you only want a single row with each Name value you might use this query:SELECT Name FROM #temp1
UNION
SELECT Name FROM #temp2
If you want to know which table the data came from and the id, but still want to avoid duplicates, use this (assuming the names are unique within each table):SELECT id = ISNULL(t1.id, t2.id),
Name = ISNULL(t1.Name, t2.Name),
SourceTable = CASE WHEN t1.id IS NULL THEN 'temp2' ELSE 'temp1' END
FROM #temp1 t1
FULL JOIN #temp2 t2 ON t1.Name = t2.Name
If you want to suppress duplicates within the tables as well as between them:SELECT id = ISNULL(t1.id, t2.id),
Name = ISNULL(t1.Name, t2.Name),
SourceTable = CASE WHEN t1.id IS NULL THEN 'temp2' ELSE 'temp1' END
FROM ( SELECT id=MIN(id), Name FROM #temp1 GROUP BY Name) t1
FULL JOIN ( SELECT id=MIN(id), Name FROM #temp2 GROUP BY Name) t2 ON t1.Name = t2.Name
May 22, 2017 at 2:00 pm
there's no race condition that i see.
Luis's test tests that one table or the other has rows; i thought it was very elegant.
IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)
if i'ts false, the UNION ALL works fine,as one or both tables have no rows. if it's true, it's just the intersected values.
Lowell
May 23, 2017 at 1:39 am
Thank you all for the suggestions.
Thank you John ,Luis, Patrick, Scott and Lowell.
May 23, 2017 at 6:49 am
Anjan@Sql - Tuesday, May 23, 2017 1:39 AMThank you all for the suggestions.
Thank you John ,Luis, Patrick, Scott and Lowell.
Do you understand the solutions presented here?
May 23, 2017 at 9:21 am
Lowell - Monday, May 22, 2017 2:00 PMthere's no race condition that i see.Luis's test tests that one table or the other has rows; i thought it was very elegant.
IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)if i'ts false, the UNION ALL works fine,as one or both tables have no rows. if it's true, it's just the intersected values.
What I did as a test was to make a construct where a condition was tested and as a result, insert into another table a select containing another test of that same result. What I found is that during concurrent access, it was possible for this condition to change between the time of the test in the "if" statement and the time of the execution of the "insert" statement chosen as a result of the test in the "if" statement.
Now, in the OP's case, Luis advises doing a "select" as a result of an "if" statement test. All I'm saying is that I don't know for a fact that between the time of the test contained in the "if" statement and the execution of the subsequently chosen "select", the condition would without fail remain in the same state as it was during the instant the "if" statement test was carried out, obviously if isolation mode "serializable" is chosen, I'm guessing that it would, but Luis did not specify this in his post.
Obviously its important to the OP that the correct select be executed or otherwise there would be no need for Luis' "if" statement.
Now granted, with #temp tables, there IS no concurrent access as its only available to the local connection (obviously barring any parallelism which shouldn't be a concern anyways), but what I've experienced here is that folks post their situations using #temp tables and single user access where in actuality they could be describing permanent tables being read and updated in an environment with concurrent connections or processes accessing and updating those same permanent tables.
May 23, 2017 at 9:29 am
FULL OUTER JOIN?
May 23, 2017 at 10:52 am
patrickmcginnis59 10839 - Tuesday, May 23, 2017 9:21 AMLowell - Monday, May 22, 2017 2:00 PMthere's no race condition that i see.Luis's test tests that one table or the other has rows; i thought it was very elegant.
IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)if i'ts false, the UNION ALL works fine,as one or both tables have no rows. if it's true, it's just the intersected values.
What I did as a test was to make a construct where a condition was tested and as a result, insert into another table a select containing another test of that same result. What I found is that during concurrent access, it was possible for this condition to change between the time of the test in the "if" statement and the time of the execution of the "insert" statement chosen as a result of the test in the "if" statement.
Now, in the OP's case, Luis advises doing a "select" as a result of an "if" statement test. All I'm saying is that I don't know for a fact that between the time of the test contained in the "if" statement and the execution of the subsequently chosen "select", the condition would without fail remain in the same state as it was during the instant the "if" statement test was carried out, obviously if isolation mode "serializable" is chosen, I'm guessing that it would, but Luis did not specify this in his post.
Obviously its important to the OP that the correct select be executed or otherwise there would be no need for Luis' "if" statement.
Now granted, with #temp tables, there IS no concurrent access as its only available to the local connection (obviously barring any parallelism which shouldn't be a concern anyways), but what I've experienced here is that folks post their situations using #temp tables and single user access where in actuality they could be describing permanent tables being read and updated in an environment with concurrent connections or processes accessing and updating those same permanent tables.
I agree with the fact that concurrency could cause an issue if the OP is not working with temp tables. However, I see no other way of making this work in an efficient way.
But this should solve the race condition, but will also add more reads.
(SELECT id, name FROM #temp1 WHERE EXISTS(SELECT * FROM #temp2)
INTERSECT
SELECT id, name FROM #temp2 WHERE EXISTS(SELECT * FROM #temp1))
UNION ALL
SELECT id, name FROM #temp1 WHERE NOT EXISTS(SELECT * FROM #temp2)
UNION ALL
SELECT id, name FROM #temp2 WHERE NOT EXISTS(SELECT * FROM #temp1);
May 23, 2017 at 10:57 am
Just UNION (not UNION ALL) the two tables:
SELECT *
FROM #temp1
UNION
SELECT *
FROM #temp2
ORDER BY <column_name>
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 23, 2017 at 11:13 am
There's not enough info on the role of the id column. If you only want unique names, and one table contains (5,"SSIS") while the other contains (12, "SSIS"), a UNION query of both columns will produce two rows. And if you get the row (5, "SSIS") in the result you know the id value (presumably a primary key) but not the table it resides in. I think the UNION ALL solutions need the added column that identifies the source table, as shown in several solutions.
If only a list of unique names is desired, then a UNION query of just the Name columns is the simplest solution. Otherwise a clearer description of requirements is needed.
May 23, 2017 at 4:46 pm
autoexcrement - Tuesday, May 23, 2017 9:29 AMFULL OUTER JOIN?
That was my first thought.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply