CODEWITHSUNDEEP
mysql
Rajneel Joshi
Rajneel Joshi

Reputation: 131

Condition in MySQL query

Apologize if I am wrong, I have 3 tables in my database by name user, view_history, online_status

User

 user_id    first_name    last_name
    2         jhon         watson
    4         boob         hasel
    5         hena         Montena

view_history

user_id    pv_user_id
 4            2
 5            2

online_status

user_id
  4

BRIEF:

1)user table is user basic information of user,
2)view_histroy table is profile viewer,that is by above `view_history` table user `4` had looked into user `2` profile etc.
3)online_status table with one column user_id consist records of user id's who are online, if they are offline then record will be deleted.

So, Now I want a query to list out who has looked into 2 profile with their online status I had written a query below: 

SELECT
      view_history.user_id, 
      view_history.pv_date, 
      user.first_name, 
      user.last_name, 
      online_status.user_id
   FROM
      view_history, 
      user, 
      online_status
   WHERE
        view_history.user_id = user.user_id
    AND online_status.user_id = user.user_id
    AND view_history.pv_user_id = 2

But I am not getting the information of user 5 who is offline.

Upvotes: 1

Views: 732

Answers (5)

Ajay Kadyan
Ajay Kadyan

Reputation: 1079

In this case you have to use LEFT JOIN to get all information of user with online status and history.

SELECT
vh.user_id,
vh.pv_date,
u.first_name,
u.last_name,
os.user_id
FROM user u
LEFT JOIN view_history vh ON vh.user_id = u.user_id
LEFT JOIN online_status os ON os.user_id = u.user_id
WHERE u.user_id = 2

if you are using Inner join then only those user's information comes whose online.

Upvotes: 0

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

  1. Since we are looking for users who have looked into a user profile we need to add a filter on pv_user_id column. Also for left joins, I have found that its better to put filters in the ON condition of the left join so that they are applied before the results are generated

  2. The inner join to online_status restricts to users who are online

  3. I have reworked @Bohemian's query below

    SELECT
 view_history.user_id,
 view_history.pv_date,
 user.first_name,
 user.last_name,
 online_status.user_id
FROM user
INNER JOIN online_status ON online_status.user_id = user.user_id
LEFT JOIN view_history ON view_history.user_id = user.user_id AND pv_user_id = 2

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

You need to use the modern JOIN syntax, which allows your to do a LEFT JOIN, which is what you need:

SELECT
    view_history.user_id,
    view_history.pv_date,
    user.first_name,
    user.last_name,
    online_status.user_id
FROM user
LEFT JOIN view_history ON view_history.user_id = user.user_id
LEFT JOIN online_status ON online_status.user_id = user.user_id
WHERE user.user_id = 2

A LEFT JOIN means you still get a row even if there's no matching row(s) (columns from the left joined table will have null values if there's no matching row)

I've reordered the tables into a more sane order, and changed the where clause to look up the user record.

I recommend you use LEFT JOIN on view_history too, in case there's no history (yet).

Also, this syntax makes it easy to see what's a join predicate, versus what's a filter predicate

Upvotes: 1

Raptor
Raptor

Reputation: 54212

Your clause AND online_status.user_id = user.user_id excludes offline user's information. Consider using LEFT JOIN.

Upvotes: 0

Jorge Aguirre
Jorge Aguirre

Reputation: 2857

Yes, because you have the condition

online_status.user_id = user.user_id

And on the online_status table, there is only user_id 4.

Upvotes: 0

Related Questions