Solved

MySQL intermingled ORDERing of selected rows from dissimilar tables

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

688 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