By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Trying to UNION three queries to one, not working, getting error !

P: 67
Hi friends, I'm having three queries that works perfectly ...

Expand|Select|Wrap|Line Numbers
  1. SELECT ib.id as id, ib.titre as title, ib.date_expire as date_fin, ib.created_at as date_creation, eb.content as content, ''
  2. FROM tbl_announce AS ib
  3. LEFT JOIN tbl_announce_content AS eb ON ib.id = eb.rap_id
  4. WHERE ib.lang = 'fr'
  5. AND (
  6. ib.date_expire = '0000-00-00'
  7. OR ib.date_expire >= '2008-02-01'
  8. )
  9. AND ib.publish =1
  10. GROUP BY ib.id
  11. ORDER BY ib.date_publish DESC
Expand|Select|Wrap|Line Numbers
  1. (
  2. SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
  3. FROM events WHERE date_fin >= '2008-02-01'
  4. AND lang = 'fr'
  5. AND publish =1
  6. )
  7. UNION (
  8. SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
  9. FROM events WHERE date_fin = '0000-00-00'
  10. AND lang = 'fr'
  11. AND publish =1
  12. )
  13. ORDER BY sort_by, date_fin ASC , date_creation ASC
Expand|Select|Wrap|Line Numbers
  1. (
  2. SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
  3. FROM job
  4. WHERE date_fin >= '2008-02-01'
  5. AND lang = 'fr'
  6. AND publish =1
  7. )
  8. UNION (
  9. SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
  10. FROM job
  11. WHERE date_fin =0000 -00 -00
  12. AND lang = 'fr'
  13. AND publish =1
  14. )
  15. ORDER BY sort_by, date_fin ASC , date_creation ASC
I wanted to make one query from these three ... so I tried an UNION ALL

Expand|Select|Wrap|Line Numbers
  1. (
  2. SELECT ib.id as id, ib.titre as title, ib.date_expire as date_fin, ib.created_at as date_creation, eb.content as content, ''
  3. FROM tbl_announce AS ib
  4. LEFT JOIN tbl_announce_content AS eb ON ib.id = eb.rap_id
  5. WHERE ib.lang = 'fr'
  6. AND (
  7. ib.date_expire = '0000-00-00'
  8. OR ib.date_expire >= '2008-02-01'
  9. )
  10. AND ib.publish =1
  11. GROUP BY ib.id
  12. ORDER BY ib.date_publish DESC
  13.  
  14. ) UNION ALL (
  15.  
  16. (
  17. SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
  18. FROM events WHERE date_fin >= '2008-02-01'
  19. AND lang = 'fr'
  20. AND publish =1
  21. )
  22. UNION (
  23. SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
  24. FROM events WHERE date_fin = '0000-00-00'
  25. AND lang = 'fr'
  26. AND publish =1
  27. )
  28. ORDER BY sort_by, date_fin ASC , date_creation ASC
  29.  
  30. ) UNION ALL (
  31.  
  32. (
  33. SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
  34. FROM job
  35. WHERE date_fin >= '2008-02-01'
  36. AND lang = 'fr'
  37. AND publish =1
  38. )
  39. UNION (
  40. SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
  41. FROM job
  42. WHERE date_fin =0000 -00 -00
  43. AND lang = 'fr'
  44. AND publish =1
  45. )
  46. ORDER BY sort_by, date_fin ASC , date_creation ASC)
But this is not working ... :(

Getting error -

Expand|Select|Wrap|Line Numbers
  1. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (
  2. SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
  3. FROM ' at line 22
Wondering, can anyone help to figure out what's going wrong here ... ?
Feb 1 '08 #1
Share this Question
Share on Google+
1 Reply


P: 67
Ho, this issue is solved, thanks to Rudy. Here is the solution -

Expand|Select|Wrap|Line Numbers
  1. ( SELECT ib.id                           
  2.        , ib.titre as title               
  3.        , ib.date_expire as date_fin      
  4.        , ib.created_at as date_creation  
  5.        , eb.content as content           
  6.        , 0 as sort_by                    
  7.     FROM tbl_announce AS ib              
  8.   LEFT                                   
  9.     JOIN tbl_announce_content AS eb      
  10.       ON eb.rap_id = ib.id               
  11.    WHERE ib.lang = 'fr'                  
  12.      AND ( ib.date_expire = '0000-00-00' 
  13.         OR ib.date_expire >= '2008-02-01'
  14.          )                               
  15.      AND ib.publish =1 )
  16. UNION ALL
  17. ( SELECT id, title, date_fin, date_creation, content             
  18.        , CASE WHEN date_fin = '0000-00-00' THEN 1                
  19.                                            ELSE 0 END  AS sort_by
  20.     FROM events                                                  
  21.    WHERE ( date_fin = '0000-00-00'                               
  22.         OR date_fin >= '2008-02-01'                              
  23.          )                                                       
  24.      AND lang = 'fr'                                             
  25.      AND publish = 1 )
  26. UNION ALL
  27. ( SELECT id, title, date_fin, date_creation, content                                           
  28.        , CASE WHEN date_fin = '0000-00-00' THEN 1                
  29.                                            ELSE 0 END  AS sort_by
  30.     FROM job                                                  
  31.    WHERE ( date_fin = '0000-00-00'                               
  32.         OR date_fin >= '2008-02-01'                              
  33.          )                                                       
  34.      AND lang = 'fr'                                             
  35.      AND publish = 1 )
  36. ORDER 
  37.     BY sort_by
  38.      , date_fin
  39.      , date_creation
Feb 3 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.