Non Duplicate rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Non Duplicate rows

Author

Topic

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-20 : 12:34:52

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

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-08-20 : 12:50:30

non-duplicate = unique....it's a lot shorter word!!!

select student_id, count(*) from table1
group by student_id
having count(*) = 1

a query on the lines of the above is what you need.....play with the fields in the select statement to get your desired output....remember the same fields need to be referenced in the group by clause.

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-20 : 12:55:42

Thanks for the reply. But I need to pull from all four tables at once not from each table. I knew for sure there are no duplicates when you talk each table separately.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-20 : 13:07:39

you need to UNION all of the tables together (use UNION ALL).

and think about redesigning your database and reading some good books on database theory.

- Jeff

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-08-23 : 08:21:05

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…

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-23 : 11:43:55

if i get this correctly:

you insert your union into a #temp table or table variable and do
select Discinct col1, ... from #temp

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-23 : 12:11:20

What the Dr. said

or


SELECT * FROM (
		SELECT * FROM Table1 UNION ALL
		SELECT * FROM Table2 UNION ALL
		SELECT * FROM Table3 UNION ALL
		SELECT * FROM Table4) AS XXX
  WHERE StudentId IN (
			  SELECT StudentId FROM (
						   SELECT StudentId, Col1, Col2 FROM Table1 UNION ALL
						   SELECT StudentId, Col1, Col2 FROM Table2 UNION ALL
						   SELECT StudentId, Col1, Col2 FROM Table3 UNION ALL
						   SELECT StudentId, Col1, Col2 FROM Table4
						 ) AS YYY
			GROUP BY StudentId
			   HAVING COUNT(*) = 1	
		    ) AS ZZZ

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources