Solved

MySQL intermingled ORDERing of selected rows from dissimilar tables

Posted on 2011-02-22
5
422 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

15 Experts available now in Live!

Get 1:1 Help Now