willsherwood
asked on
MySQL intermingled ORDERing of selected rows from dissimilar tables
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
etc.
any suggestions if this would work (and how to do a temp table), or a completely different strategy??
many thanks!
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,
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
etc.
any suggestions if this would work (and how to do a temp table), or a completely different strategy??
many thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Making sure i got your strategy, please advise if i'm on the right track: I do need to "propagate" each ID from the tables A,B,C; and only do this for rows corresponding to $ThisProductID.
Does the below adapt your suggestion to address that?
SELECT *
FROM (SELECT A_ID AS ID, CreateDateTime,'A' AS SourceTable
FROM tbl_A WHERE Product_ID=$ThisProductID
UNION ALL
SELECT B_C_ID AS ID, CreateDateTime,'C' AS SourceTable
FROM tbl_C WHERE Product_ID=$ThisProductID
) AS t1
ORDER BY CreateDateTime DESC
Does the below adapt your suggestion to address that?
SELECT *
FROM (SELECT A_ID AS ID, CreateDateTime,'A' AS SourceTable
FROM tbl_A WHERE Product_ID=$ThisProductID
UNION ALL
SELECT B_C_ID AS ID, CreateDateTime,'C' AS SourceTable
FROM tbl_C WHERE Product_ID=$ThisProductID
) AS t1
ORDER BY CreateDateTime DESC
Yes, that should give you the result set.
ASKER
much appreciated! i'm on it.
ASKER
thanks