Doing some DBA-ing recently I came across a table that contained dates and times for orders stored in a bit of an unusual way. Instead of having columns for date and for time, they were both stored in the same column as different rows!
I hadn’t come across this before arrangement before but I needed to get an order reference that occurred at a precise time and date. I had to think about this one for a few minutes because it was so weird. The answer of course is a join on the table itself.
SELECT A.meta_value AS date, B.meta_value AS time, A.order_item_id
FROM orders_meta A, orders_meta B
WHERE A.meta_value="2020-02-01"
AND B.meta_value="18:35"
AND A.order_item_id = B.order_item_id
ORDER BY A.order_item_idThis SQL statement gets the individual rows matching the dates and time and then joins them on the order_item_id column.