Page 1 of 1

combining 2 queries using union

Posted: Mon Jan 29, 2007 9:39 am
by hame22
Hi

I have a PHP script that is trying to combine 2 queries using the union operator.

My code is as follows:

Code: Select all

$sql = " 
            SELECT 
            DATE_FORMAT(news_date, '%d-%m-%Y') as news_date2, news_id, news_headline, news_summary, news_body, news_image, news_image_detail, news_author, news_tj, news_pdf, news_pdf, news_tj_issue, news_price, news_rating, news_votes, news_tags ,
                MATCH(news_headline, news_body) AGAINST('$k') AS score 
                FROM news 
            WHERE MATCH(news_headline, news_body) AGAINST('$k')".$query."
            
            UNION
            SELECT thread_id from hrd where parent_id IS NULL ORDER BY score DESC";
            
		   if(isset($offset) && (isset($limit)))
		   {
		   	 $sql .= " Limit $offset, $limit";
		   }
I currently experience the following error: You have an error in your SQL syntax near 'UNION SELECT thread_id from hrd where parent_id IS NULL ORDER BY sco' at line 8

any ideas as to where I am going wrong?

thanks in advance

Posted: Mon Jan 29, 2007 9:59 am
by mikeq
depends on the version of MySQL you are using, are you using a version that supports UNIONS? They were not supported until version 4.0.0

On the basis that you are using the correct version then you need to parenthesise your select statements

(SELECT * FROM blah)
UNION
(SELECT * from bling)
LIMIT 10;

This applies the limit to the whole query

To limit each individual query

(SELECT * FROM blah)
UNION
(SELECT * from bling LIMIT 10)

a 2 second search of google with the words UNION LIMIT would have shown this link first

http://dev.mysql.com/doc/refman/5.0/en/union.html