CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
Kilise
Kilise

Reputation: 1089

Mutliple left joins return double rows

I have 4 tables

I'm trying to get friend requests and new messages in a single query. It works well but as I left join 'friendsreuqests' I get multiple rows.

Here is my query:

SELECT username,
  f1.userid as reqId,
  m1.msg, m1.userid, 
  p1.picHash, p1.extension, UNIX_TIMESTAMP( m1.sent ) AS date
FROM users
  LEFT JOIN pictures p1 ON p1.userid = users.id
  LEFT JOIN messages m1 ON m1.contactid = users.id AND m1.delivered =0
  LEFT JOIN friendrequests f1 ON f1.contactid = users.id AND f1.delivered=0 AND f1.request =1
WHERE users.id =7
ORDER BY date ASC

This is the MySQL result:

Kungen  3     gregegerg     1    dc825b1c8a35593be4d172db7    jpg    1369839537
Kungen  12    gregegerg     1    dc825b1c8a35593be4d172db7    jpg    1369839537
Kungen  3     HEJH          1    dc825b1c8a35593be4d172db7    jpg    1369839540
Kungen  12    HEJH          1    dc825b1c8a35593be4d172db7    jpg    1369839540

As you see there result is doubled. I want to get only two rows at this point. If I have two new messages but no new friends requests those 2 rows should be NULL.

Upvotes: 0

Views: 170

Answers (4)

mhafellner
mhafellner

Reputation: 468

The problem is that after the first two joins you get this if im right:

Kungen  gregegerg       1   dc825b1c8a35593be4d172db7   jpg     1369839537
Kungen  HEJH            1   dc825b1c8a35593be4d172db7   jpg     1369839540

now with joining the friendrequests table on f1.contactid = users.id you join the requests for user 3 and 12 on both entries with the userid 1 resulting in:

Kungen  3   gregegerg       1   dc825b1c8a35593be4d172db7   jpg     1369839537
Kungen  12  gregegerg       1   dc825b1c8a35593be4d172db7   jpg     1369839537
Kungen  3   HEJH            1   dc825b1c8a35593be4d172db7   jpg     1369839540
Kungen  12  HEJH            1   dc825b1c8a35593be4d172db7   jpg     1369839540

Now to get the right result you would need to join the friendrequests table on something that connects with the message or timestamp as this is the only column that differs.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want to get friend requests and messages in a single query, then I would expect the query to have a union all. Your query is doing a join between the tables. This would add information about friend requests as additional columns. You want it in additional rows.

I'm not sure which fields in the friends table correspond to which fields that you are requesting. But here is an example of a query using union all:

SELECT username, users.id as reqId, m1.msg, m1.userid, 
       p1.picHash, p1.extension, UNIX_TIMESTAMP( m1.sent ) AS date
FROM users LEFT JOIN
     pictures p1 ON p1.userid = users.id LEFT JOIN
     messages m1 ON m1.contactid = users.id AND m1.delivered =0 
WHERE users.id =7
union all
SELECT username, users.id as reqId, NULL, NULL, 
       NULL, NULL, UNIX_TIMESTAMP( f1.sent ) AS date
FROM users LEFT JOIN
     friendrequests f1
     ON f1.contactid = users.id AND f1.delivered=0 AND f1.request =1
ORDER BY date ASC

Upvotes: 2

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

If you LEFT JOIN both friend requests and messages like you do you will get No of messages times number of friend requests in your result set. In you example you have 2 requests and 2 messages resulting in (2*2=) 4 rows.

Perhaps you should consider using a union instead? With a union you will be able to get one row for each friend request and one row for each message. That should suit your needs better. You will still get 4 rows in your example (2+2), though.

Upvotes: 1

David Jashi
David Jashi

Reputation: 4511

Adding GROUP BY clause with all fields in it will surely help, but you will have to decide, which value for second field you want to keep (3 or 12). If you definitely know, that there are always 2 rows with that field differing, than you can select both MIN(m1.msg) and MAX(m1.msg)

Upvotes: 0

Related Questions