Mysql Union repeating results

Question

I have the following select but it repeats the results of the second inner select 3 times.

Can anyone tell me why this is.

Also when I get the results how can I know which table the results came from. Home_content or facilities_table.

SELECT * FROM (SELECT hm_id, hm_name, hm_summary, 
MATCH (hm_name, hm_summary) AGAINST  ('test') AS score FROM home_content 
WHERE MATCH (hm_name, hm_summary) AGAINST ('test') UNION SELECT  fac_id,fac_name,          
fac_summary, MATCH (fac_title, fac_summary) AGAINST ('test') AS score FROM 
facilities_table WHERE MATCH (fac_title, fac_summary) AGAINST ('test')) a 
ORDER BY SCORE DESC

Thanks in advance

Answer 1

Can't see why you are getting duplicates from this query unless:

1. There are duplicates in the source table
2. The same data appears in home_content and facilties_table (perhaps one is a view of the other?)

The second part of question as to deciding which table the contents of the union comes from is easily address by adding a constant column to each query of the union giving you something like this:

SELECT * FROM (SELECT 1,hm_id, hm_name, hm_summary, 
MATCH (hm_name, hm_summary) AGAINST  ('test') AS score FROM home_content 
WHERE MATCH (hm_name, hm_summary) AGAINST ('test') UNION SELECT  2,fac_id,fac_name,          
fac_summary, MATCH (fac_title, fac_summary) AGAINST ('test') AS score FROM 
facilities_table WHERE MATCH (fac_title, fac_summary) AGAINST ('test')) a 
ORDER BY SCORE DESC

In this case the initial column should be 1 for the home_content table and 2 for the facilities_table. Obviously string constants could be used in a similair style if that suited you better.