MySQL intermingled ORDERing of selected rows from dissimilar tables
Posted on 2011-02-22
I have a design with three table that i'm stuck with (inherited) in a currently working system.
The three tables store three different types of text messages/comments that were independently inserted over time.
Each table has a creation date field, which i want to use as the ORDER BY sorter for displaying the comment information in chronological order from the collection/merger of the three tables.
tbl_A A_ID; Product_ID; CreatedDateTime; (other fields not displayed in this part of the report)
tbl_B B_ID; Product_ID; CreatedDateTime;
tbl_C C_ID; Product_ID; CreatedDateTime;
Other than brute-force programming a set of (nested) loops in PHP to find the next newest of the three (with three separate queries each loop iteration), selecting and displaying that next date, then setting a temp variable for WHERE > that datetime for future loop iterations...
With my limited knowledge of MySQL i can think of two possible approaches, but i have no idea if these are possible.
1. Sort a subSELECTion of the columns of the tables that ARE similar (see above: tableID, WHERE Product_ID=$ThisProductID, CreatedDateTime), and then sort the amalgamated collection of rows by date, and somehow splice in A, B, or C.
2. Similarly, maybe there's a way to perhaps create a temp table for all related rows (records) in A, B, C for a given Product_ID, and labeling which table the ID came from, ORDERed BY CreateDateTime (which would be across all three tables' contributions for this Product_ID)
Thus i'd end up with a simpler query results loop to go thru and get the details for
each ID **IN THE CORRESPONDING TABLE** (where the data came from)
Perhaps this temp table looks like:
ID SourceTable SourceTableID
1 C 8
2 A 3
3 A 6
4 B 2
in this example the results WHILE loop would entail getting details from tbl_C ID=8
followed by tbl_A ID=3
any suggestions if this would work (and how to do a temp table), or a completely different strategy??