New to SQL, how do I use results from a SELECT call on TableA to SELECT against TableB?

Question

I'm using phpMyAdmin to query the database.

I want to select all the entries in TableA where Date='2010-08-01'. Then I want to use the SubID attributes of the items in the result table to find all the entries in TableB with matching SubIDs. I know I have to use a JOIN somewhere, but I am not sure how or where.

I searched StackOverflow, and there appear to be a lot of similar questions, but none as basic (aka noob) as mine. I couldn't really figure out what they were talking about. To give you an idea of what I know, I started learning SQL today, and I read through the W3 School tutorial on SQL. I feel like the answer will be extremely obvious and I will smack myself for not figuring it out.

Thanks a lot.

Answer 1

A good reference is http://www.w3schools.com/sql

What you need is:

SELECT *
FROM TableA a
JOIN TableB b on a.SubID = b.SubID
WHERE a.Date = '2010-08-01'

---

Edit as per the other posts - a JOIN is likely to be more efficient, however if you have multiple records in TableB with the same SubID, you will get duplicate records from TableA returned.

Answer 2

You actually don't need a JOIN - just a subquery to filter TableB. Like so, for example:

SELECT SubID
FROM TableB
WHERE SubID IN
    (SELECT SubID
     FROM TableA
     WHERE Date='2010-08-01');

That said, you can use a join, as per @ck's answer.

Answer 3

If you're looking for a join, ck's post offers a simple solution.

Another way of approaching this would be to use a subquery (this came to my mind upon reading your question title):

SELECT * FROM TableB WHERE SubID IN
    (SELECT SubID FROM TableA WHERE Date = '2010-08-01')

The outer SELECT query (selecting from TableB) chooses rows whose SubIDs are found in the result set of the inner SELECT query (selecting from TableA). So for example if TableA turns up five rows with these SubIDs:

• 8
• 11
• 12
• 27
• 35

Then rows will be selected from TableB whose SubIDs are IN (8, 11, 12, 27, 35).