Hi friends, I'm having three queries that works perfectly ... - 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, ''
-
FROM tbl_announce AS ib
-
LEFT JOIN tbl_announce_content AS eb ON ib.id = eb.rap_id
-
WHERE ib.lang = 'fr'
-
AND (
-
ib.date_expire = '0000-00-00'
-
OR ib.date_expire >= '2008-02-01'
-
)
-
AND ib.publish =1
-
GROUP BY ib.id
-
ORDER BY ib.date_publish DESC
- (
-
SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
-
FROM events WHERE date_fin >= '2008-02-01'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
UNION (
-
SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
-
FROM events WHERE date_fin = '0000-00-00'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
ORDER BY sort_by, date_fin ASC , date_creation ASC
- (
-
SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
-
FROM job
-
WHERE date_fin >= '2008-02-01'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
UNION (
-
SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
-
FROM job
-
WHERE date_fin =0000 -00 -00
-
AND lang = 'fr'
-
AND publish =1
-
)
-
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 - (
-
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, ''
-
FROM tbl_announce AS ib
-
LEFT JOIN tbl_announce_content AS eb ON ib.id = eb.rap_id
-
WHERE ib.lang = 'fr'
-
AND (
-
ib.date_expire = '0000-00-00'
-
OR ib.date_expire >= '2008-02-01'
-
)
-
AND ib.publish =1
-
GROUP BY ib.id
-
ORDER BY ib.date_publish DESC
-
-
) UNION ALL (
-
-
(
-
SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
-
FROM events WHERE date_fin >= '2008-02-01'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
UNION (
-
SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
-
FROM events WHERE date_fin = '0000-00-00'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
ORDER BY sort_by, date_fin ASC , date_creation ASC
-
-
) UNION ALL (
-
-
(
-
SELECT id, title, date_fin, date_creation, content, 0 AS sort_by
-
FROM job
-
WHERE date_fin >= '2008-02-01'
-
AND lang = 'fr'
-
AND publish =1
-
)
-
UNION (
-
SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
-
FROM job
-
WHERE date_fin =0000 -00 -00
-
AND lang = 'fr'
-
AND publish =1
-
)
-
ORDER BY sort_by, date_fin ASC , date_creation ASC)
But this is not working ... :(
Getting error - - #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 (
-
SELECT id, title, date_fin, date_creation, content, 1 AS sort_by
-
FROM ' at line 22
Wondering, can anyone help to figure out what's going wrong here ... ?
1 2167
Ho, this issue is solved, thanks to Rudy. Here is the solution - - ( SELECT ib.id
-
, ib.titre as title
-
, ib.date_expire as date_fin
-
, ib.created_at as date_creation
-
, eb.content as content
-
, 0 as sort_by
-
FROM tbl_announce AS ib
-
LEFT
-
JOIN tbl_announce_content AS eb
-
ON eb.rap_id = ib.id
-
WHERE ib.lang = 'fr'
-
AND ( ib.date_expire = '0000-00-00'
-
OR ib.date_expire >= '2008-02-01'
-
)
-
AND ib.publish =1 )
-
UNION ALL
-
( SELECT id, title, date_fin, date_creation, content
-
, CASE WHEN date_fin = '0000-00-00' THEN 1
-
ELSE 0 END AS sort_by
-
FROM events
-
WHERE ( date_fin = '0000-00-00'
-
OR date_fin >= '2008-02-01'
-
)
-
AND lang = 'fr'
-
AND publish = 1 )
-
UNION ALL
-
( SELECT id, title, date_fin, date_creation, content
-
, CASE WHEN date_fin = '0000-00-00' THEN 1
-
ELSE 0 END AS sort_by
-
FROM job
-
WHERE ( date_fin = '0000-00-00'
-
OR date_fin >= '2008-02-01'
-
)
-
AND lang = 'fr'
-
AND publish = 1 )
-
ORDER
-
BY sort_by
-
, date_fin
-
, date_creation
Sign in to post your reply or Sign up for a free account.
Similar topics
by: lyn.duong |
last post by:
Hi,
I have a large table (about 50G) which stores data for over 7 years. I
decided to split this table up into a yearly basis and in order to
allow minimum changes to the applications which...
|
by: _link98 |
last post by:
Problem: getting SQL0181N for queries on nicknames to remote
Union-All-View.
Can't see what I'm doing wrong yet, maybe someone has seen this before.
Environment: UDB ESE 8.1 + FIXPAK 9A, on...
|
by: s_gregory |
last post by:
The mdb is considerable size 70 +- mb. A complex union query was
working well, but when an additional union select... was added into
the query, selecting identical fields from a different source,...
|
by: Missy |
last post by:
We’ve recently upgraded our computer system to XP. My union
query (which was working perfectly for years) now returns
hieroglyphics instead of invoice numbers. When I run the 2 queries...
|
by: Susan Bricker |
last post by:
For those of you who have been following my posts - they all pertain to
a Dog Competition Organization's Database.
There are three classes that the dogs can participate:
NOVICE, OPEN, and...
|
by: mikes |
last post by:
I have 2 separate queries, which effectively are the same except they
draw data from separate tables. Both tables are (design-wise)
identical, only the data is different. for each query, there are...
|
by: spam |
last post by:
If I run the following query in Access 2002 then I get the expected
result:
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;
I get the contents of both tables with no duplicates. If I...
|
by: Killer42 |
last post by:
Hi all.
As has been mentioned here recently, I have a front-end database which just includes some queries and a bunch of (rather large) linked tables. For reasons of (very poor) performance, I had...
|
by: rileyjane |
last post by:
Hello,
I am running a Union query in Access 2003 that combines two other queries. When I run each query individually, they open fine with no problems. But when I try to run the Union query, I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |