How to select from table A only where a relationship between the table A row and a specific row in B exists?

Question

lets say I have two MySQL tables, table A and table B. Each table has a primary key called id and a name column. I also have a third table, table C, that contains relationships between table A and table B. Each row contains two foreign keys called a_id and b_id, which, as you might expect, correspond to ids in tables A and B.

What I want to do is select a random set of 10 table A rows, but only select rows that have a relationship with specific entries in table B. I don't know which entries I'm looking for ahead of time, and I will start with their names. The names will be provided via query parameters.

I understand I should probably start with this:

SELECT * FROM `A`
ORDER BY RAND()
LIMIT 10

But I don't know how to structure the where clause.

Answer 1

You need something like this:

SELECT *
FROM `A` a
INNER JOIN `C` c ON
    a.ID = c.a_id AND
    c.b_id in (1,2,3,4) -- your entries here

-- order and limit as you wish
ORDER BY RAND() 
LIMIT 10