Post reply

Comparing Tables with Union against another Table with conditions

  • July 28, 2018 at 5:08 pm

    #366588

    Hello,

    I have this query,

    select *
    from
    (select [KD]
    from [dbo].[Tab1]

    union

    select [KD]
    from [dbo].[Tab2]

    where [KD] like '%abc%' or [KD] like '%def%') as C

    where not exists

    (select [KD]
    from [dbo].[Tab3] as T
    where c.[KD]=t.[KD]
    )

    I want the output to be only where it combines tabl 1 and 2 via the union, and compares those results against tab 3, and only displays results where this condition is met, where [KD] like '%abc%' or [KD] like '%def%') from the union is not in tab3.

    However it is not delivering the expected result. I think there is a problem with scope.  How can I rewrite, where it works?

    Thanks

  • July 28, 2018 at 7:50 pm

    #1999376

    Your parentheses look wrong.

    SELECT x.*
    FROM 
    (     SELECT a
           FROM table1
           WHERE...
      UNION ALL
           SELECT b
           FROM table2
           WHERE... ) x
    WHERE NOT EXISTS (SELECT 1 FROM Table3 WHERE table3.Column = x.A)

    ?

  • July 28, 2018 at 8:51 pm

    #1999379

    Ok let me try, hmm still missing some data. Here's the updated version I have based on your feedback

    select *
    from

    (select [KD]
    from [dbo].[Tab1]
    where [KD] like '%abc%' or [KD] like '%def%'

    union

    select [KD]
    from [dbo].[Tab2]
    where [KD] like '%abc%' or [KD] like '%def%') as C

    where not exists

    (select [KD]
    from [dbo].[Tab3] as T
    where c.[KD]=t.[KD]
    )

  • July 28, 2018 at 10:29 pm

    #1999380

    Without sample data (CREATE TABLE and INSERT scripts), there's no way for us to test your query for you. So all we can give you is reasonably educated guesses. If you can provide data, you have a good chance of getting a tested solution.

    Read Jeff's article Forum Etiquette: How to post data/code on a forum to get the best help... it has all the information you need for us to help you.

  • July 29, 2018 at 3:45 am

    #1999384


    select kd
    from
    (
     select kd from table1
     intersect select kd from table2
     except select kd from table3
    ) U
    where U.kd like '%abc%' or U.kd like '%def%'

  • July 31, 2018 at 12:09 pm

    #1999741

    Hey guys. thanks for your help. I went back and re checked and I had overlooked a result. Your help is really appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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