• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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!
0
willsherwood
Asked:
willsherwood
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
Why don't you UNION all the tables and then sort the result set. Something like this,
SELECT * 
    FROM (SELECT Product_ID,CreateDateTime,'A' AS SourceTable 
            FROM tbl_A 
          UNION ALL 
          SELECT Product_ID,CreateDateTime,'B' AS SourceTable 
            FROM tbl_B 
          UNION ALL 
          SELECT Product_ID,CreateDateTime,'C' AS SourceTable 
            FROM tbl_C) AS t1 
ORDER BY CreateDateTime DESC

Open in new window

0
 
willsherwoodAuthor Commented:
wow,  neat!
thanks
0
 
willsherwoodAuthor Commented:
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
0
 
SharathData EngineerCommented:
Yes, that should give you the result set.
0
 
willsherwoodAuthor Commented:
much appreciated!   i'm on it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now