Post reply

Please Help me!!

  • August 21, 2009 at 5:22 am

    #96111

    How can i get common values among three tables?

    I mean, I got three tables, and they are similar column, called Z. I want the values which exists at least in two of these tables.

    For Example,

    Table1 - Z=1, Z=2

    Table2 - Z=1,Z=3

    Table3 - Z=1,Z=2

    The values 1 and 2 will appear to me, but the value 3 no...because it's just in one table.

    That's what I want...

    Thanks a lot!!

  • August 21, 2009 at 5:26 am

    #1042722

    Something like this ?

    Create View UnionView

    as

    Select Z from tablea

    union all

    Select Z from tableb

    union all

    select z from tablec

    go

    Select Z,count(*)

    from UnionView

    having count(*) >=2


    Clear Sky SQL
    My Blog[/url]

  • August 21, 2009 at 5:33 am

    #1042728

    There's a problem, If I have more tha one value in each table...modifying the last example, like that!!

    Table1 - Z=1, Z=2

    Table2 - Z=1,Z=3,Z=3

    Table3 - Z=1,Z=2

    My question is, in your view, the 3 will appear, right??

  • August 21, 2009 at 5:38 am

    #1042731

    select z,z1 from table1

    union

    select z,z3 from table2

    union

    select z,z1 from table3

  • August 21, 2009 at 5:42 am

    #1042732

    I think the best way to do this is,

    create View UnionView

    as

    Select distinct z from table1

    union all

    Select distinct z from table2

    union all

    select distinct z from table3

    go

    Select z,count(*)

    from UnionView

    group by z

    having count(*) >=2

    using the distinct I can avoid to return values that are duplicated in just one table..

  • August 21, 2009 at 5:43 am

    #1042733

    andersonrj18 (8/21/2009)

    There's a problem, If I have more tha one value in each table...modifying the last example, like that!!

    Table1 - Z=1, Z=2

    Table2 - Z=1,Z=3,Z=3

    Table3 - Z=1,Z=2

    My question is, in your view, the 3 will appear, right??

    In that case Yes , this should solve that

    Create View UnionView

    as

    Select distinct Z from tablea

    union all

    Select distinct Z from tableb

    union all

    select distinct z from tablec

    go

    Select Z,count(*)

    from UnionView

    having count(*) >=2

    If you require further help , please post a script to create you table(s) and populate with example data


    Clear Sky SQL
    My Blog[/url]

  • August 21, 2009 at 6:09 am

    #1042749

    Perfect...thanks!!

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

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