combining 2 queries using union

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

combining 2 queries using union

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
Post Reply