CODEWITHSUNDEEP
mysqldatabase
Jean-Nicolas
Jean-Nicolas

Reputation: 51

Substracting 2 rows in a MySQL query LEFT JOIN

SELECT * From `users` AS `User`
LEFT JOIN `selections` AS `Selections` ON (`Selections`.`user_id` = `User`.`id`)
LEFT JOIN  `clients` AS `Client` ON (`Client`.`id` = `Selections`.`client_id`)
LEFT JOIN  `client_stats` AS `ClientsStat` ON (`ClientsStat`.`date` = """DATE1""")

The thing is I would like to have the values of some fields in ClientsStat where date = "DATE1" minus the same fields but with a date #2 But I am not sure how can I do that while in a LEFT JOIN. I tried while doing an other LEFT JOIN to the same table and renaming it and subtracting but the execution time were extremely high so i guess my method was bad.

EDITING:

My result is something like:

USER => fields...
SELECTIONS => fields...
CLIENTS => fields..
CLIENT_STATS => field x,y,z on date = date 1

and so on.

I would like this:

USER => fields
USERSELECTIONS => fields...
CLIENTS => fields..
CLIENT_STATS => field x,y,z when client_stats.date = date1 MINUS field x,y,z when client_stats.date = date2

etc.

Upvotes: 0

Views: 93

Answers (2)

DRapp
DRapp

Reputation: 48169

You should be good with a double join on the stats. Performance, I would guess is based on indexes. However, are you sure you mean left-join (only required on the first table regardless of match on the second). Or... do you mean an INNER JOIN -- you are EXPECTING RECORDS ON BOTH SIDES of the join. If you are trying to subtract the values from one record date vs another, I would expect BOTH entries to be found. I've written as basis of a "JOIN" (both sides must exist) instead of "LEFT JOIN".

SELECT 
      U.* 
      CS1.x - CS2.x as XDiff,
      CS1.y - CS2.y as YDiff,
      CS1.z - CS2.z as ZDiff
   From 
      users U
         JOIN selections S
            ON U.ID = S.User_ID
            JOIN clients C
               ON S.Client_ID = C.ID
               JOIN  client_stats CS1
                  ON ( C.ID = CS1.Client_ID AND CS1.`date` = YourFirstDateVariable )
               JOIN  client_stats CS2
                  ON ( C.ID = CS2.Client_ID AND CS2.`date` = YourSecondDateVariable )

Not absolutely sure if this is what you were looking for, but I would think one element you missed was a join to the client stats table on just the date... you had no qualifier on WHICH client ID and thus was probably the failure for your performance. Ensure client_Stats has an index on (client_id,date) -- provided this IS the case that client_id was missing and IS part of the client_stats table.

Upvotes: 1

Tenakha
Tenakha

Reputation: 183

First: You have to left join onto client_stats with a field from one of your other tables. So get the join working correctly first. The way it is now - does that query run? Second: Then you can restrict the date range on ClientsStat.date using a WHERE clause on the date:

WHERE ClientsStat.date >= 'date1' AND ClientsStat.date <= 'date2'

or something like that.

(may need to see more info on tables to see exactly what you are trying to do). As a general rule - get the query working returning more rows than you need, and then work out how to pare them down.

Upvotes: 0

Related Questions