Unanswered: Return row even though search value is null?
Here's my problem. I have widgets that have unique ID's, a date that says when they were last moved, and a location. The table's structure doesn't allow me to view when the widget was moved to a certain place, it just specifies it's last movement. For instance, Widget 101 was last moved today, from Operation B. However, I want to know if that Widget was moved TO Operation B today as well. So what I need to find out is whether or not it was at Operation B yesterday or Operation A. So if I could take all the Widget ID's from today (some may have just been created today and may not exist previously), get their dates and then compare to the last Max date moved along with the operation, I would be able to tell if it was moved to Operation B. Here are some examples:
Now, can I put this into one SQL statement? I've racked my brain for a little bit am stumped. I could write two seperate queries, 1 which retrieves the date last moved for today, and 2 which grabs the max date last moved for each widget that is less than today's date, then write code which matches the two queries up. However, I know it can be simplified into one SQL statement, I just can't figure it out.
Please let me know if you need more information and thanks so much in advance!
Assume the table is as follows
select a.id, a.location, a.op_date, b.location as prev_location, b.op_date as prev_date
from wip a left outer join wip prev on a.id=prev.id
where a.op_date="today's date"
and prev.op_date = (select max(op_date)
from wip c
where c.op_date < a.op_date