UNION table query wrong data

Question

I'm new mySQL user using the UNION command to add 2 query tables. The union works except the results repeat themselves if they have the same ID. If I delete or change the ID thru phpMyAdmin you see the entry. Example:'

My day | today | 12:00
My day | today | 12:00

and change of the ID:

OK day | other | 12:01
My day | today | 12:00

And the PHP code

$query = "SELECT id,title,day,time 
          FROM $db_1 
          UNION 
          SELECT id,title,day,time 
          FROM $db_2 
          WHERE month='$month' AND year='$year' ORDER BY time" ;enter code here
$query_result = mysql_query ($query);
while ($info = mysql_fetch_array($query_result))

{
$day = $info['day'];
$event_id = $info['id'];
$events[$day][] = $info['id'];
$event_info[$event_id]['0'] = substr($info['title'], 0, 8);;
$event_info[$event_id]['1'] = $info['time'];
}

Answer 1

This should do the trick for you:

select distinct *
from
(
    SELECT 
        id,
        title,
        day,
        time 
    FROM 
        $db_1 
    UNION ALL
    SELECT 
        id,
        title,
        day,
        time 
    FROM 
        $db_2 
    WHERE 
        month='$month' 
        AND year='$year' 
)
ORDER BY time

Although group by can be used, it is less efficient. I ran into trouble with the query below and ended up running some stats as the first was returning in around 10 seconds, the second query in under half a second.

The difference is that using a group by will introduce a filesort on the subquery, which in my case caused all the problems:

mysql> EXPLAIN select
    ->  a.mCode as cCode,
    ->  a.mName as cName
    -> from
    ->  _user_UserStructure a
    -> where
    ->  a.pCode in
    ->  (
    ->          select
    ->                  b.pCode
    ->          from
    ->                  _user_userStructure b
    ->          where
    ->                  b.mCode='RBM1'
    ->                  and b.pCode!=b.cCode
    ->          group by
    ->                  b.pCode
    ->  )
    ->  and a.cCode != ''
    ->  and a.pCode != a.mCode
    ->  and a.mCode!='RBM1'
    -> group by
    ->  a.mCode,
    ->  a.mName
    -> order by
    ->  a.mName asc;
+----+--------------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+

| id | select_type        | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                                        |

+----+--------------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+

|  1 | PRIMARY            | a     | ALL   | cCode         | NULL  | NULL    | NULL | 1769 | Using where; Using temporary; Using filesort |

|  2 | DEPENDENT SUBQUERY | b     | index | NULL          | pCode | 13      | NULL |    2 | Using where; Using filesort                  |

+----+--------------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+

2 rows in set (0.00 sec)


mysql> EXPLAIN select distinct
    ->  a.mCode as cCode,
    ->  a.mName as cName
    -> from
    ->  _user_UserStructure a
    -> where
    ->  a.pCode in
    ->  (
    ->          select distinct
    ->                  b.pCode
    ->          from
    ->                  _user_userStructure b
    ->          where
    ->                  b.mCode='RBM1'
    ->                  and b.pCode!=b.cCode
    ->  )
    ->  and a.cCode != ''
    ->  and a.pCode != a.mCode
    ->  and a.mCode!='RBM1'
    -> order by
    ->  a.mName asc;
+----+--------------------+-------+----------------+---------------+-------+---------+------+------+----------------------------------------------+

| id | select_type        | table | type           | possible_keys | key   | key_len | ref  | rows | Extra                                        |

+----+--------------------+-------+----------------+---------------+-------+---------+------+------+----------------------------------------------+

|  1 | PRIMARY            | a     | ALL            | cCode         | NULL  | NULL    | NULL | 1769 | Using where; Using temporary; Using filesort |

|  2 | DEPENDENT SUBQUERY | b     | index_subquery | pCode         | pCode | 13      | func |    2 | Using where                                  |

+----+--------------------+-------+----------------+---------------+-------+---------+------+------+----------------------------------------------+

2 rows in set (0.00 sec) 

Answer 2

You could use GROUP BY

SELECT * from 
(SELECT id,title,day,time FROM $db_1 
 UNION 
 SELECT id,title,day,time FROM $db_2 
 WHERE month='$month' AND year='$year' ORDER BY time) m
GROUP BY id