Solved

MySQL intermingled ORDERing of selected rows from dissimilar tables

Posted on 2011-02-22
5
428 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
  • 3
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now