1

I have 2 MySQL tables A and B.

I would like to select only the records from B where a certain value exists in A.

Example:

A has columns: aID, Name

B has columns: bID, aID, Name

I just want the records from B for which aID exists in A.

Many thanks.

mustaccio
  • 17,405
  • 14
  • 45
  • 53
prre72
  • 677
  • 2
  • 11
  • 22
  • You need to use a join. – Harry Nov 05 '14 at 15:05
  • possible duplicate of [Select from table if record found in another table](http://stackoverflow.com/questions/18310838/select-from-table-if-record-found-in-another-table) – Harry Nov 05 '14 at 15:13

6 Answers6

5

You need to do either INNER JOIN - records that exists in both tables, or use LEFT join, to show records that exists in A and matching IDs exists in B

A good reference:

joins

Andrew
  • 7,351
  • 13
  • 59
  • 113
1

You need to make a join, and if you don't want to retrieve anything from table b, just return values from table a.

This should work

select b.* from b join a on b.aID=a.aID

Chris Lear
  • 6,452
  • 1
  • 16
  • 25
0

Below query will also work and will be effective

SELECT * FROM B 
WHERE B.aID IN (SELECT DISTINCT aID FROM A)
Bikash Pradhan
  • 106
  • 7
  • This will work, but inefficiently. See eg http://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why – Chris Lear Nov 05 '14 at 15:14
0

You can use join like this.

Select b.col1,b.col2... From tableB b inner join table tableA a on b.field = a.field
Andy
  • 46,308
  • 56
  • 161
  • 219
Gaurav Jain
  • 444
  • 3
  • 13
0

You just need a simple inner join between tables A and B. Since they are related on the aID column, you can use that to join them together:

SELECT b.*
FROM tableB b
JOIN tableA a ON a.aID = b.aID;

This will only select rows in which the aID value from tableB exists in tableA. If there is no connection, the rows can't be included in the join.

While I recommend using a join, you can also replace it with a subquery, like this:

SELECT *
FROM tableB
WHERE aID NOT IN (SELECT aID FROM tableA)
AdamMc331
  • 15,982
  • 9
  • 67
  • 128
-1

Have you tried using a LEFT JOIN?

SELECT b.* FROM tableB b LEFT JOIN tableA a ON b.aID = a.aID
DJx
  • 19
  • 5
  • A left join will not work. This will include ALL rows from B, regardless of whether or not it has a connection with table A. – AdamMc331 Nov 05 '14 at 16:44
  • This will work actually. Isn't b.aID = a.aID used to check if aID from table B exists in A? How quick of you to downvote an answer. – DJx Nov 05 '14 at 17:07
  • because it's a left join, so all rows from B are returned. If a row in b does not have b.aid = a.aid, it is still returned in this query. – AdamMc331 Nov 05 '14 at 17:08