I am trying to return all data, from all users, before a date/time which is unique to each user.
I can return all the data for the exact date for each user, no problem, but I don't know how to expand my query to return dates less than or equal to my key date.
I have a 2 table SQL database.
Table 1 - "sales" has a confirmation record of all transactions.
WHERE user_id IN (SELECT user_id FROM sales)
AND record_date IN (SELECT min(record_date) FROM user_data WHERE purchase_made = 1 GROUP BY user_id);
"Now all I really want is to be able to change that second "IN" to "<="
so make it a correlated subqery by coorlating the records we eliminate the need for the "IN" and now can use
SELECT * FROM user_data UD WHERE user_id IN (SELECT user_id FROM sales) AND record_date <=(SELECT min(record_date) FROM user_data UD2 WHERE UD2.purchase_made = 1 and UD2.User_ID = UD.User_ID);
Another approach would be to add user_Id to the subquery and instead of a subquery make it a inline view that you join to based on the user_ID and then on the join criteria compare the dates.
SELECT * FROM user_data UD INNER JOIN (SELECT min(record_date) mrd, User_ID FROM user_data WHERE purchase_made = 1) UD2 ON UD.user_Id = UD2.user_Id AND UD.record_date <= UD2.RecordDate WHERE user_id IN (SELECT user_id FROM sales)