CODEWITHSUNDEEP
phpsqljoin
Calvin Fowler
Calvin Fowler

Reputation: 31

Left/Inner join with AND,WHERE, and OR

I have 2 tables:

Friends

  ID  |  Profile  |  Check
------+-----------+---------
  int |    int    |   bool

Where ID is person who sent original friend request, Profile is ID of person request was sent to, and Check is 0 = request pending; 1 = request accepted.

Messages

  OwnerID  |  ...
-----------+-------
    int    |  ...

Where OwnerID is ID of member who wrote the message.

So basically, what I am looking at is first:

select all rows from friends_list table where ID or Profile is equal to the memberID cookie. BUT heres my plight: If I send another user a friend request, then my ID is placed in the ID field and their ID is placed in the Profile field of the friend_list table. But if another user requests a friend request from me, then my ID would go into the Profile field and theirs would go in the ID field.

So, I would have a row where ID = 1, and Profile = 2. And the next row would be ID = 3 and Profile = 1. Now, both users with IDs 2 and 3 are friends of mine (ID 1), So I need to show all messages from 1, 2, and 3 (mine and my two friends) where check = 1

Upvotes: 0

Views: 77

Answers (1)

DaveRandom
DaveRandom

Reputation: 88647

I think what you want is this:

(
    SELECT m.*
    FROM Messages m
    INNER JOIN Friends f ON m.OwnerID = f.ID
    WHERE f.Profile = ?
) UNION (
    SELECT m.*
    FROM Messages m
    INNER JOIN Friends f ON m.OwnerID = f.Profile
    WHERE f.ID = ?
)

You need to look at this a two separate queries, I don't think you can sensibly do this with just a combination of JOINs.

Assuming you are using MySQL, this should not return duplicate rows, because the default modifier for UNION is DISTINCT. Other RDBMS engines may require you to explicitly state this or use some other work-around (for example a GROUP BY on the actual message ID).

You may also want to add an m.Check = 1 condition to the WHERE clause of both queries, to ensure you only get messages where the friend request has been accepted.

Obviously the above is designed to be used as a prepared statement, where both placeholders would be substituted with the same data - the memberID cookie.

Upvotes: 1

Related Questions