-1

I have two tables in my database 

 ---------               ---------
|    A    |             |    B    |
|---------|             |---------|
| id      |             | id      |
| name    |             | a_id    |
 ---------              | name    |
                         ---------

I want to select all the records in table A only if there is a record in table B that points to the record in table A ( B.a_id = A.id).

How can i achieve this? And I'd like an explanation so I understand how it's done.

justijn
  • 139
  • 1
  • 10
  • ... where id in (select ... from b) – jarlh Nov 15 '16 at 15:31
  • i feel stupid for asking this... I'm working on a very old project from somebody else. I changed the query before asking this question to the inner join solution but it didn't had any effect. Turned out he repopulated the listbox (with the results of the sql query) via an ajax call. So no matter what i did, the result were the same. :/ – justijn Nov 15 '16 at 15:54

3 Answers3

1

Use an INNER JOIN:

SELECT DISTINCT A.*
FROM A
    INNER JOIN B ON B.a_id = A.id

Because of the INNER JOIN, for each row in table A, MySQL finds all the rows from B that match the JOIN condition (B.a_id = A.id).

The SELECT clause instruct it to return only the columns from table A (A.*) but because of the JOIN, a row in A can match more than one row in B. The DISTINCT clause takes care to avoid having the same output row multiple times in the result set.

axiac
  • 68,258
  • 9
  • 99
  • 134
0
SELECT *
FROM a
WHERE id IN (SELECT a_id from b)
SQLChao
  • 7,709
  • 1
  • 17
  • 32
0

This is a basic INNER JOIN

SELECT a.*
FROM a
INNER JOIN b ON a.ID = b.a_ID

Inner joins will select all the records from the specified table(s) where there is a match on the join criteria (the ON operator).

Graham
  • 7,431
  • 18
  • 59
  • 84
msturek
  • 541
  • 6
  • 17