Mysql join query

Question

I'm using three tables in the database and query that I need should combine all three tables. The tables look like:

Table A:

     id   |   name   |   status   
---------------------------------
     5    |   john    |     1
     7    |   mike    |     1
     8    |   jane    |     0

Table B:

 send-id  |  receive-id  |  notification
-------------------------------------------
    12    |      5       |       1
    5     |      23      |       1
    8     |      14      |       1
    19    |      7       |       2
    14    |      5       |       1

Table C:

  registered-id  |   status-reg
 ----------------------------------   
       5         |      7
       7         |      7
       8         |      7
       9         |      3

I need to list the users who have the status "1" from the table A and the status "7" from the table C and that these users are not listed in the table B column "receive-id" with the value "2" in column "notification".

The result in this example will be:

    id   |   name    |   status   |  notification
 --------------------------------------------------
     5   |   john    |     1      |

Users with ID numbers 7 and 8 would be excluded from the list. User with ID# 7 because it is located in Table B in the field receive-id with a value of 2 in table notification and the user with ID 8 because it has a status 0 in Table A.

How can I do that in one query?

Thanks for any help.

score 2 · Answer 1 · answered Apr 22 '12 at 16:59

Select ID, Name, Status, Notification
FROM TableA A
LEFT JOIN TableB B on B.receive-id = a.ID
 and b.notification <> 2
INNER JOIN tableC C on A.ID = C.Registered-id
WHERE a.status=1
and c.status-reg=7

I think you want the all users even if they don't have a record in table B so long as status is 1 and 7. Thus I think a left join is needed, and limits must be imposed on the join then (Unless you want to handle nulls in the where clause)

score 1 · Answer 2 · answered Apr 22 '12 at 16:56

Let's try (untested, may contain errors):

SELECT a.id, a.name, a.status, b.notification
FROM a
JOIN c ON (a.id = c.registered-id)
JOIN b ON (a.id = b.receive-id)
WHERE a.status = 1
AND c.status-reg = 7
AND b.notification <> 2

Hope this gets you on the right track.

Mysql join query

2 Answers2

Linked