Looking for best approach

  • 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'

  • SELECT
         id
    ,    Name
    ,    'temp1' AS SourceTable
    FROM #temp1
    UNION ALL
    SELECT
         id
    ,    Name
    ,    'temp2'
    FROM #temp2

    John

  • 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;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Monday, May 22, 2017 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;

    RACE CONDITION!

    lol well not if using local temp tables so never mind 🙂

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you all for the suggestions.
    Thank you John ,Luis, Patrick, Scott and Lowell.

  • Anjan@Sql - Tuesday, May 23, 2017 1:39 AM

    Thank you all for the suggestions.
    Thank you John ,Luis, Patrick, Scott and Lowell.

    Do you understand the solutions presented here?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lowell - Monday, May 22, 2017 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.

    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.

  • FULL OUTER JOIN?


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • patrickmcginnis59 10839 - Tuesday, May 23, 2017 9:21 AM

    Lowell - Monday, May 22, 2017 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.

    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);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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) "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".

  • 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.

  • autoexcrement - Tuesday, May 23, 2017 9:29 AM

    FULL OUTER JOIN?

    That was my first thought.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply