August 20, 2004 at 10:53 am
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
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
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
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
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
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