MySQL Joins. match two conditions in two columns

Question

I am trying to fetch data from the 2 tables using joins. But I don't know how to do it. I have read about joins but it wasn't making any sense since I am new.

I have two tables.

 Playgrounds           Inspection 

   id   u_id          id    playground_id
   1    2              1     1
   2    2              2     2

In playgrounds table id is the playground id and is unique and u_id is the user of the playground.

In inspection table id is unique and playground_id is used as a foreign key from playgrounds table's id.

My problem is that I have to pass id and u_id to the the query in playgrounds table then it should select id from playground table. Then it should select every thing from inspection table based on that id .

Hope I have explained it properly.

Any help is much appreciated.

Thanks in advance

Can you show what you have done so far? – P.Salmon Oct 02 '16 at 07:57 — P.Salmon, Oct 02 '16 at 07:57
make a join on `playground_id` – Zeeshan Oct 02 '16 at 11:48 — Zeeshan, Oct 02 '16 at 11:48

score 1 · Answer 1 · answered Oct 02 '16 at 13:34

JOIN operations, whether INNER JOIN, LEFT JOIN, or RIGHT JOIN, have this overall syntax.

   table_expression JOIN table_expression ON boolean_expression

When the Boolean expression is true, the JOIN concatenates the matching rows of the two tables into a single row in the result.

In your case, you want the ON clause's Boolean expression to say

  Playgrounds.id = Inspection.playground_id

because that's how you know a row from Inspection relates to a row in Playgrounds. Accordingly, a statement like

 SELECT Inspection.id AS Inspection_ID,
        Playgrounds.id AS Playground_ID,
        Playgrounds.u_id AS User_ID
   FROM Playgrounds
   JOIN Inspection ON Playgrounds.id = Inspection.playground_id
  WHERE Playgrounds.u_id = something
    AND Playgrounds.id = something_else

See how it goes? Your SELECT makes a new table, sometimes called a result set, by JOINing the rows of two existing tables on the criteria you choose in the ON clause.

You can put anything you want into the ON clause. You could put

Playgrounds.id = Inspection.playground_id OR Inspection.id < 20

or

Playgrounds.id = Inspection.playground_id OR Inspection.scope = 'citywide'

for example (if you had a scope column in the Inspection table).

But, don't go there until you master the basic JOIN operation.

MySQL Joins. match two conditions in two columns

1 Answers1