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
382 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
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now