CODEWITHSUNDEEP
mysql
mdnba50
mdnba50

Reputation: 379

Mysql SELECT UNION returning wrong results?

I have this single query.

$sql = "SELECT * FROM `my_table` WHERE type!='' AND (page_option='0' OR page_option='1') ORDER BY type ASC";

The first three results for the column scenario returned are a1,d3,e1.

Now when i do a UNION ALL with other identical tables:

$sql = "SELECT * FROM `my_table` 
                    WHERE type!='' AND (page_option='0' OR page_option='1')
                    UNION ALL
                    SELECT * FROM `my_table_2`
                    WHERE type!='' AND (page_option='0' OR page_option='1')
                    UNION ALL
                    SELECT * FROM `my_table_3`
                    WHERE type!='' AND (page_option='0' OR page_option='1')
                    ORDER BY type ASC";

I loop through the results and the first three columns for my_table are e1,e1,e1.

Each table has a column called type, which identifies the table the result comes from. So for the union query results i added a condition to display only scenarios from my_table

if($type=="my_table") {
  echo $scenario;
}

The scenarios should be identical to the single query but they aren't.

How do i solve?

Upvotes: 0

Views: 155

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Your assumption is wrong. Order Isnt guaranteed unless you use ORDER BY

In your case you are order only by type so the order of other fields can be random

You can find a nice explanation and examples here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx

Upvotes: 2

Related Questions