2

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
  • 18,234
  • 16
  • 48
  • 57
prre72
  • 697
  • 2
  • 12
  • 23
  • 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

7

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,619
  • 13
  • 63
  • 117
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,592
  • 1
  • 18
  • 26
1

Below query will also work and will be effective

SELECT * FROM B 
WHERE B.aID IN (SELECT DISTINCT aID FROM A)
Bikash Pradhan
  • 116
  • 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
1

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
  • 16,492
  • 10
  • 71
  • 133
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
  • 49,085
  • 60
  • 166
  • 233
Gaurav Jain
  • 444
  • 3
  • 13
-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