I have 2 tables: A and B. A has duplicate records, and so does table B. I need to pick all the records that appear...
in table A that have matching records in table B. How do I do it?
Table A: 1,2,3,1 Table B: 2,1,3,3
Result Table C: 2,1,1,3
As is common with SQL, there are several ways to skin this cat. Perhaps the most simple would be to use an EXISTS clause with a correlated sub-query, like this:
select * from A where exists ( select * from B where B.Value = A.Value)
select A.* from A inner join ( select DISTINCT Value from B ) B1 on A.Value = B1.Value
For More Information
- What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.