?
Solved

Oracle Query help: I want to be able to identify the values in TableA that are also located in TableB

Posted on 2011-02-28
3
Medium Priority
?
389 Views
Last Modified: 2012-05-11
What query can I use for this? This is for an oracle database, and I'm using PL/SQL.
I want to be able to identify the values in TableA that are also located in TableB

TableA

FirstName            LastName
David            Smith
Mike            Smith
Arron            Smith


TableB

FirstName            LastName
Mark            Smith
Mike            Smith
Arron            Smith


So after I run my query, it will return...
Mike            Smith
Arron            Smith
0
Comment
Question by:silentthread2k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 35002288
try this

SELECT A.FirstName, A.LastName
FROM tableA A
      INNER JOIN tableB B ON A.FirstName = B.FirstName AND A.LastName = B.LastName
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 35002370
If tableB can have duplicates, you can use

SELECT DISTINCT A.FirstName, A.LastName
FROM tableA A
INNER JOIN tableB B
  ON A.FirstName = B.FirstName AND A.LastName = B.LastName

or

SELECT A.FirstName, A.LastName
FROM tableA A
WHERE EXISTS (
  SELECT * FROM tableB B
  WHERE A.FirstName = B.FirstName AND A.LastName = B.LastName)
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35003847
You can go with the above as cyberkiwi said :

 
SELECT *
FROM TABLEA A
WHERE EXISTS
  (SELECT 1 FROM tableb WHERE FIRSTNAME =A.FIRSTNAME AND lastname =A.LASTNAME
  );
  
  
  
SELECT DISTINCT A.FirstName, A.LastName
FROM tableA A
INNER JOIN TABLEB B
  ON A.FirstName = B.FirstName AND A.LastName = B.LastName;
  
  
SELECT a.*
FROM TABLEA A ,
  TABLEB B
WHERE A.FIRSTNAME =B.FIRSTNAME
AND A.LASTNAME    =B.LASTNAME;

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question