CODEWITHSUNDEEP
mysqlsql
LeSpotted44
LeSpotted44

Reputation: 67

Left join sql query

I want to get all the data from the users table & the last record associated with them from my connection_history table, it's working only when I don't add at the end of my query

 ORDER BY contributions DESC

(When I add it, I have only the record which come from users and not the last connection_history record)

My question is: how I can get the entire data ordered by contributions DESC?

SELECT * FROM users LEFT JOIN connections_history ch ON users.id = ch.guid
    AND EXISTS (SELECT 1
        FROM   connections_history ch1
            WHERE  ch.guid = ch1.guid
                HAVING Max(ch1.date) = ch.date)

Upvotes: 0

Views: 2570

Answers (3)

Dwipam Katariya
Dwipam Katariya

Reputation: 144

We can use nested queries to first check for max_date for a given user and pass the list of guid to the nested query assuming all the users has at least one record in the connection history table otherwise you could use Left Join instead.

select B.*,X.* from users B JOIN (
select A.* from connection_history A 
where A.guid = B.guid and A.date = (
select max(date) from connection_history where guid = B.guid) )X on 
X.guid = B.guid 
order by B.contributions DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

The order by should not affect the results that are returned. It only changes the ordering. You are probably getting what you want, just in an unexpected order. For instance, your query interface might be returning a fixed number of rows. Changing the order of the rows could make it look like the result set is different.

I will say that I find = to be more intuitive than EXISTS for this purpose:

SELECT *
FROM users u LEFT JOIN
     connections_history ch
     ON u.id = ch.guid AND
        ch.date = (SELECT  Max(ch1.date)
                   FROM connections_history ch1
                   WHERE ch.guid = ch1.guid
                  )
ORDER BY contributions DESC;

The reason is that the = is directly in the ON clause, so it is clear what the relationship between the tables is.

Upvotes: 4

radarbob
radarbob

Reputation: 5101

For your casual consideration, a different formatting of the original code. Note in particular the indented AND suggests the clause is part of the LEFT JOIN, which it is.

SELECT * FROM users 
LEFT JOIN connections_history ch ON 
  users.id = ch.guid
  AND EXISTS (SELECT 1
              FROM   connections_history ch1
              WHERE  ch.guid = ch1.guid
              HAVING Max(ch1.date) = ch.date
             )

Upvotes: 0

Related Questions