Post reply

non duplicate Rows

  • August 20, 2004 at 10:53 am

    #152268

    I have 4 tables with same column and data type and Student_ID is PK. I need to pull non duplicate rows from all four tables. Can some throw light on this?

     

    Thanks

    Shas3

  • August 22, 2004 at 11:11 am

    #520097

    What do you mean by "non duplacate rows"?

    Is something like this suitable for you?:

    select * from Table1

    left join Table2

     on Table1.Student_ID = Table2.Student_ID

     and Table1.Col2 = Table2.Col2

     and Table1.Col3 = Table2.Col3

     and Table1.Col4 = Table2.Col4

    left join Table3

     on Table1.Student_ID = Table3.Student_ID

     and Table1.Col2 = Table3.Col2

     and Table1.Col3 = Table3.Col3

     and Table1.Col4 = Table3.Col4

    left join Table4

     on Table1.Student_ID = Table4.Student_ID

     and Table1.Col2 = Table4.Col2

     and Table1.Col3 = Table4.Col3

     and Table1.Col4 = Table4.Col4

    where Table2.Student_ID is null

    and Table3.Student_ID is null

    and Table4.Student_ID is null



    Bye
    Gabor

  • August 22, 2004 at 7:46 pm

    #520115

    A possible approach would be to read the data from each of the tables into one temporary table which has the name (or alias) of the source table as a column.  Then all you need to do is ..

    select ... from temporary table group by ... having count(*) < 4

    Michael

  • August 23, 2004 at 6:35 am

    #520149

    I take my word back. Actually the tables are not exactly the same however there are some common columns. I need all the common columns + one more column for which may have to do a join after the union. But now I do need to eliminate the duplicate values for each column values after combining the results

    Shas3

  • August 23, 2004 at 12:11 pm

    #520220

    Just a suggestion,

    If you don't want to remove the duplicates, but just get a result set that doesn't contain duplicates, an easy way to do this would be using UNION.

    However, you will probably have to join in the other information as a separate second step, so it might not be the best solution for your problem...

    select col_1, col_2 -- All your common columns

    from table_1

    UNION

    select col_1, col_2

    from table_2

    UNION

    select col_1, col_2

    from table_3

    ...

  • August 23, 2004 at 12:14 pm

    #520221

    Yes I have done UNION and a join to get other missing column. But I do need to find out the duplicate values for each column

    Shas3

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

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