Issue in mysql Join query

Question

I am developing a social website.I have an option called alerts which shows pending friend requests and unread messages.My query is following:

SELECT sk_friends.frndship_from_user_id, sk_messages.msg_from_user_id, sk_messages.msg_text, sk_messages.msg_date FROM sk_friends INNER JOIN sk_messages WHERE sk_messages.msg_to_user_id = '$user_id' AND sk_friends.frndship_to_user_id ='$user_id' AND sk_friends.frndship_status = 'pending' AND sk_messages.msg_status='unread' ORDER BY sk_friends.fndship_date ASC, sk_messages.msg_date ASC;

sk_friends and ak_messages are tables.
msg_from_user_id is the id of sender
frndship_from_user_id is the id of the user who sends the request
$user_id is the id of the login user

Each row data is appearing twice. I dont know why does it happen.

you have missed ON statement in join query. check dev.mysql.com/doc/refman/5.0/en/join.html to apply inner join. — Ripa Saha
– Ripa Saha, Commented Feb 12, 2013 at 10:22
Use "ON" in place of "WHERE" in the JOIN-segment of your query. — illuzive
– illuzive, Commented Feb 12, 2013 at 10:23

Husman · Accepted Answer · 2013-02-12 10:21:49Z

4

Your inner join does not have an ON clause. Try adding one, that should remove double results.

answered Feb 12, 2013 at 10:21

Husman

6,93910 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chintan · Accepted Answer · 2013-02-12 11:03:17Z

Try this :

SELECT sf.frndship_from_user_id, sm.msg_from_user_id, sm.msg_text,sm.msg_date FROM sk_friends sf , sk_messages sm WHERE sm.msg_to_user_id = sf.frndship_to_user_id AND sm.msg_to_user_id = '$user_id' AND sf.frndship_status = 'pending' AND sm.msg_status='unread' ORDER BY sf.fndship_date ASC,sm.msg_date ASC;

Collectives™ on Stack Overflow

Issue in mysql Join query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related