• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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

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
silentthread2k
Asked:
silentthread2k
1 Solution
 
tigin44Commented:
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
 
cyberkiwiCommented:
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
 
pinkurayCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now