[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MySQL intermingled ORDERing of selected rows from dissimilar tables

Posted on 2011-02-22
5
Medium Priority
?
467 Views
Last Modified: 2012-05-11
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
Comment
Question by:willsherwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34957726
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
 

Author Closing Comment

by:willsherwood
ID: 34957901
wow,  neat!
thanks
0
 

Author Comment

by:willsherwood
ID: 34957936
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
 
LVL 41

Expert Comment

by:Sharath
ID: 34957977
Yes, that should give you the result set.
0
 

Author Comment

by:willsherwood
ID: 34958082
much appreciated!   i'm on it.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question