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.
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
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.
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.