Problem solve
Get help with specific problems with your technologies, process and projects.
Selecting records that exist in two tables
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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
in table A that have matching records in table B. How do I do it?
Example:
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.
