CODEWITHSUNDEEP
mysqlsql
javy
javy

Reputation: 275

SQL - return data from a column using 2 different IDs

I'm trying to build a user based message system. I'd like messages to show up that are sent between friends, sort of like a facebook news feed.

I'm using a query:

SELECT friend_user_2, user_id, user_name, sent_id, sent_from_id, sent_to_id
FROM friends, users, sent_messages 
WHERE (user_id = sent_from_id AND friend_user_1 = '$user_id' AND sent_from_id = friend_user_2) 
OR (friend_user_1='$user_id' AND friend_user_2=sent_to_id AND user_id=sent_to_id) ORDER BY sent_id

The problem im running into, is that the user_id of the sender and receiver aren't identified separately, it only returns the user_id of the friend. I would think that a table join is necessary to attach the names somehow, or (if this is even possible) nesting another query within the array that's returned by the query which then queries for each name individually.

Any ideas??

Upvotes: 0

Views: 86

Answers (2)

Dmitry Ovsyanko
Dmitry Ovsyanko

Reputation: 1416

If the friends table contains unordered pairs of friends and each message is related to exactly one such pair (from + to), you could consider to change the design of your database so that each message get a reference to this pair (say, friends_id).

This extra data can speed up your queries because SELECTs with different join conditions combined with OR are nearly inoptimizable.

Upvotes: 0

xpapad
xpapad

Reputation: 4456

You can get the two user ids using table name or aliases, like:

SELECT users.user_id, friends.user_id AS friend_user_id ...

Upvotes: 2

Related Questions