Mysql join tables

Question

I have three tables in the database.

The first table is one that contains user information and that looks like this:

    id     |     name   |     status    
---------------------------------------
    1      |    john    |      1
    2      |    helen   |      1
    3      |    mike    |      1
    4      |    tina    |      1
    5      |    jim     |      0
    6      |    nina    |      1

The second table contains registered users for a certain service:

   sid     |   status  
------------------------
    1      |     1
    2      |     1

The third table contains registered users for another service:

   oid     |   status  
------------------------
    3      |     1
    4      |     1

I have to make a request that will find all users with the status “1” from the second and third tables, and then get the name and identifier of all these users from the first table (with information about users). The result from this example will look like this:

   id     |     name   |     status    
---------------------------------------
    1      |    john    |      1
    2      |    helen   |      1
    3      |    mike    |      1
    4      |    tina    |      1

What will this query look like? Should I use an INNER JOIN?

Answer 1

INNER JOIN - the right approach.

Request will be

SELECT user.id, user.name, user.status
FROM table1 AS user
INNER JOIN table2 AS service1 ON service1.sid = user.id
INNER JOIN table3 AS service2 ON service2.oid = user.id
WHERE service1.status = 1 AND service2.status = 1

, , 1 2,

SELECT user.id, user.name, user.status
FROM table1 AS user
WHERE user.id IN (SELECT sid FROM table2 WHERE status=1) OR
      user.id IN (SELECT oid FROM table3 WHERE status=1)

Answer 2

, , ( service1) 1, (service2) 1:

INNER JOIN:

SELECT u.id, u.name, u.status
FROM users u
JOIN service1 s ON s.sid = u.id AND s.status=1
JOIN service2 o ON o.oid = u.id AND o.status=1;

:

SELECT u.* FROM users u
WHERE u.id IN (SELECT s.id FROM service1 s WHERE s.sid = u.id AND s.status=1)
AND u.id IN (SELECT s.id FROM service2 o WHERE o.oid = u.id AND o.status=1)

:

SELECT u.* FROM users u
WHERE u.id IN (SELECT s.id FROM service1 s WHERE s.sid = u.id AND s.status=1)
OR u.id IN (SELECT s.id FROM service2 o WHERE o.oid = u.id AND o.status=1)

Answer 3

select * from firsttable 
inner join table2 on id = sid and table2.status = 1
inner join table3 on id = oid and table3.status = 1