How to join more than 2 tables in MySQL?

Question

I have 3 tables i want to join all tables each other. But my 3rd table not working.

See my table -

users

 id       | username       |is_active
----------|----------------|------------
 1        | chinu          | 1  
 2        | sradhanjali    | 1
 3        | User3          | 0

settings

 id       | user_id   |  public_msg_notification
----------|-----------|---------------------------
 1        | 1         | 1  
 2        | 2         | 1
 3        | 3         | 1

friends

 id       | user_id   |  friend_id  |  is_block
----------|-----------|---------------------------
 1        | 3         | 1           |  0
 2        | 1         | 2           |  1
 3        | 3         | 2           |  0

Query

SELECT a.username FROM users a
  JOIN settings b
  JOIN friends c ON(a.id=c.user_id OR a.id=c.friend_id)
  WHERE a.username IN('john','piter','rahul','sradhanjali')
    AND a.id != '1' AND a.is_active=1
    AND a.id=b.user_id AND b.public_msg_notification=1
    AND c.is_block=0 GROUP BY a.username

I have run this query in my local only sradhanjali username fetched. But this user is_block=1 in the friends table.

I think My third table friends not working. I want to show that result those usernmes where is_block=0. In above data my output should be zero(0) But I am getting 1 record while execute above query.

@CodeGodie as per your comment I have added `ON` in my query. No changes found — Chinmay235, Aug 18 '15 at 17:46
in order to give you the correct answer let us know what is your expected output? — CodeGodie, Aug 18 '15 at 18:01
You are correct there is no *need* to add `ON` for `settings`, but your `a.id=b.user_id` is exactly the same thing as an `ON` and is better read when you write it as an `ON` — Cayce K, Aug 18 '15 at 18:04
possible duplicate of [SQL join multiple tables](http://stackoverflow.com/questions/9853586/sql-join-multiple-tables) — drneel, Aug 18 '15 at 18:33
@drneel it sadly isn't cause OP isn't having a problem getting multiple tables it is more of a problem that they don't know how to get the data that they are trying to get exactly.. I have already told them that isn't a question for SO as structured, but tried to edit my answer and hopefully it will help a little. I also sadly went ahead and voted to close ;( — Cayce K, Aug 18 '15 at 19:34

Cayce K · Accepted Answer · 2015-08-18T19:30:41.177

1

We had a chat discussion and I think this question is not meant to be on SO for the most part. I did promise if I could figure it out I would try to provide some insight. At this point I think this is a correct approach, but it is very specific to this instance.

SELECT u.username FROM users u 
JOIN (SELECT 
    IF(u.id=f.user_id, f.friend_id, f.user_id) as ids
          FROM users u 
          JOIN friends f ON (f.user_id=u.id OR f.friend_id=u.id)
                WHERE
        u.id=$SOME_ID AND f.is_block=0) friends ON (u.id=friends.ids)
JOIN settings s ON (s.user_id=friends.ids)
WHERE  s.public_msg_notification=1 AND u.is_active=1
GROUP BY friends.ids

By trying to be too specific you aren't able to open up the query any more and have to do a nested query inside. This should get all users you are friends with THEN see which users are accepting public notifications and are active. I'm fearing this will fail. But this at the least will put you in the right direction.

edited Aug 18 '15 at 19:30

answered Aug 18 '15 at 17:59

Cayce K

2,288
1
22
35

My output should be 0 because no condition satisfied. – Chinmay235 Aug 18 '15 at 18:06
1

No. based on your conditions it is satisfied. If you want to make it non satisfied you must remove the `OR` in the `ON` and it will satisfy to no one – Cayce K Aug 18 '15 at 18:13
1

@Chinu you got to realize that when you're writing these queries that you must break it down step by step. And the `OR` in your query allows for a dual case in which 1 and 2 are valid outputs. If you remove it then you will get 0. This query will return 1 and 2 based on your tables. – Cayce K Aug 18 '15 at 18:16
Yes, but this case `2` id may be `user_id` column or `friend_id` column in the `settings` table – Chinmay235 Aug 18 '15 at 18:20
I do not want to be rude, but that sentence did not make sense. As you have it laid out the id `2` is only in relation to `a.id AND (c.user_id OR c.friend_id)` – Cayce K Aug 18 '15 at 18:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/87304/discussion-between-chinu-and-cayce-k). – Chinmay235 Aug 18 '15 at 18:22
Thanks dear for your helping nature. I really appreciate you – Chinmay235 Aug 19 '15 at 05:18

How to join more than 2 tables in MySQL?

1 Answers1