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
384 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
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Foxpro errors 4 30
Field name with special character (Ñ) in Oracle 11 48
Merging spreadsheets 8 37
SQL Workhours Count beetween Workhours 3 16
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

770 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