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.
Advertisement
Answer
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