Solved

Need help with ORDER BY on this MySQL many to many and UNION

Posted on 2011-03-14
4
370 Views
Last Modified: 2012-06-21
Have been getting some help with the following query.

At the moment the query makes the union of the two tables and then ORDERs the result.

I would like to have that changed so that the two tables are ORDERED by projects.project_position BEFORE the UNIOIN occurs.

That way each table can be ORDERED separately rather than as one, at the end. I'm just not sure how to add the two ORDER BY to the query.


SELECT * FROM (SELECT projects.project_short_name, projects.project_synopsis, projects.project_position, projects.project_name 
FROM projects 

INNER JOIN p_c ON p_c.project_id = projects.project_id 
INNER JOIN c ON c.category_id = p_c.category_id 

WHERE ( c.category_name = 'Type-A' OR c.category_name = 'Type-B' OR c.category_name = 'Type-C' OR c.category_name = 'Type-D' ) 
AND NOT projects.project_visibility =0 

UNION SELECT concat( projects.project_short_name, '_grey' ), projects.project_synopsis, projects.project_position, projects.project_name 
FROM projects 

INNER JOIN p_c ON p_c.project_id = projects.project_id 
INNER JOIN c ON c.category_id = p_c.category_id 
WHERE NOT c.category_name = 'Type-A' AND NOT c.category_name = 'Type-B' AND NOT c.category_name = 'Type-C' AND NOT c.category_name = 'Type-D' 
AND NOT projects.project_visibility =0 
AND projects.project_id NOT IN 
( SELECT DISTINCT projects.project_id FROM projects 

INNER JOIN p_c ON p_c.project_id = projects.project_id 
INNER JOIN c ON c.category_id = p_c.category_id 
WHERE c.category_name = 'Type-A' OR c.category_name = 'Type-B' OR c.category_name = 'Type-C' OR c.category_name = 'Type-D' )
) 
AS t1 ORDER BY cast(project_position as signed integer)

Open in new window

0
Comment
Question by:sany101
4 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 35134136
Have you tried

SELECT * FROM (SELECT projects.project_short_name, projects.project_synopsis, projects.project_position, projects.project_name
FROM projects ORDER BY `projects.project_position` ASC

INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
0
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 35134164
One way to accomplish this is to add a fake column to each union query, and then add it to the ORDER BY.

So for example, below I add a column called 'SortBy' to each query and then sorts by that value first. As such the first table, since it has a '1' for SortBy will always be sorted on top, and then the second table will always be on the bottom.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35134220
You have two different data sets. Assume data_set_1 and data_set_2. When you do UNION between these two data sets, it eliminates all duplicate records and give you one  record for the combination of columns mentioned in the SELECT clause. Now you have to apply the ORDER BY on the whole set after doing the UNION operation to get the result in proper order.
If you apply ORDER BY individually, then it will display the records in the order of first data set and then next data set. However if you are interested in that, you can try this.
SELECT * 
  FROM (SELECT projects.project_short_name, 
               projects.project_synopsis, 
               projects.project_position, 
               projects.project_name 
          FROM projects 
               INNER JOIN p_c 
                 ON p_c.project_id = projects.project_id 
               INNER JOIN c 
                 ON c.category_id = p_c.category_id 
         WHERE ( c.category_name = 'Type-A' 
                  OR c.category_name = 'Type-B' 
                  OR c.category_name = 'Type-C' 
                  OR c.category_name = 'Type-D' ) 
               AND NOT projects.project_visibility = 0 
         ORDER BY CAST(project_position AS SIGNED INTEGER)) AS t1 
UNION 
SELECT * 
  FROM (SELECT CONCAT(projects.project_short_name, '_grey'), 
               projects.project_synopsis, 
               projects.project_position, 
               projects.project_name 
          FROM projects 
               INNER JOIN p_c 
                 ON p_c.project_id = projects.project_id 
               INNER JOIN c 
                 ON c.category_id = p_c.category_id 
         WHERE NOT c.category_name = 'Type-A' 
               AND NOT c.category_name = 'Type-B' 
               AND NOT c.category_name = 'Type-C' 
               AND NOT c.category_name = 'Type-D' 
               AND NOT projects.project_visibility = 0 
               AND projects.project_id NOT IN 
                   (SELECT DISTINCT projects.project_id 
                      FROM projects 
                           INNER JOIN p_c 
                             ON p_c.project_id = 
                                projects.project_id 
                           INNER JOIN c 
                             ON c.category_id = 
                                p_c.category_id 
                     WHERE c.category_name = 'Type-A' 
                            OR c.category_name = 'Type-B' 
                            OR c.category_name = 'Type-C' 
                            OR c.category_name = 'Type-D') 
         ORDER BY CAST(project_position AS SIGNED INTEGER)) AS t2  

Open in new window

0
 

Author Closing Comment

by:sany101
ID: 35134486
That's perfect and exactly waht I was after thanks again.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php date - compare 4 40
Hacked File Timestamps 4 49
WordPress syntax 2 25
Bootstrap collapse causes odd behavior with php loop 7 22
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…
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 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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

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

16 Experts available now in Live!

Get 1:1 Help Now