JOIN client2 c ON t.ClientID=c.ID
LEFT JOIN tocname tc ON t.TOC=tc.toc
LEFT JOIN network n ON t.NetworkID=n.ID
WHERE t.toc=1
AND SUBSTRING(t.Day,1,7) = '2013-08'
AND SUBSTRING(t.Day,1,7) = '2013-11')
UNION
(SELECT SUBSTRING(t.Day,1,7) AS Date,
a.Name AS Account
a shorter but equivalent query, you have:
(SELECT t.id, t.name, SUM(val) FROM t1 t)
UNION
(SELECT t.id, t.name, SUM(val) FROM t2 t)
GROUP BY t.id, t.name;
That does not work in 5.0 either (at least in 5.0.96):
ERROR 1064 (42000): You have an error in your SQL syntax; check
been doing what you think at all, and you need
to
select [fields] from (
select fields1 blahblah
UNION
select fields2 blahblah) unionized
group by blurb
that is, wrap the entire union in a virtual table and do the group by on that.
--
Unhappiness is discouraged
UNION
select fields2 blahblah) unionized
group by blurb
that is, wrap the entire union in a virtual table and do the group by on that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
) FROM t1 t)
UNION
(SELECT t.id, t.name, SUM(val) FROM t2 t)
GROUP BY t.id, t.name;
That does not work in 5.0 either (at least in 5.0.96):
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax
2012/06/20 14:32 -0700, Rick James
(
SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx
UNION
SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx
) ORDER BY overlap DESC;
Make it UNION ALL or UNION DISTINCT depending on whether xxx can be in both
fields of one row. UNION
BY with just the
second SELECT.
-Original Message-
From: Hal?sz S?ndor [mailto:h...@tbbs.net]
Sent: Thursday, June 21, 2012 4:07 PM
To: mysql@lists.mysql.com
Subject: UNION and ORDER BY
2012/06/20 14:32 -0700, Rick James
(
SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx
Why would using UNION cause the subqueries to be de-optimized?
explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord
where (gmtdate date_sub(current_timestamp(),interval 90 day)) and
(player1='13213' or player2='13213' ) group by variation limit 3)
shows using index
We've been experimenting with the merge engine.
But suppose that instead of using the MERGE engine I instead modified my
code to UNION ALL the shards.
Would I get worse performance? In other words, besides the convenience,
does the MERGE engine have specific performance optimizations
Hi there,
I run simple statement like this:
create table c
select * from a
union
select * from b;
where table a has 90,402,534 rows, and table b has 33,358,725 rows.
Both tables have the same three variables.
It's taken a long time, more than half an hour now. How do I make it faster?
Best
On 04/05/2010 02:06 PM, chen jia wrote:
Hi there,
I run simple statement like this:
create table c
select * from a
union
select * from b;
where table a has 90,402,534 rows, and table b has 33,358,725 rows.
Both tables have the same three variables.
It's taken a long time, more
Union does a distinct on all results. UNION ALL will avoid that.
Regards,
Gavin Towey
-Original Message-
From: chen.1...@gmail.com [mailto:chen.1...@gmail.com] On Behalf Of chen jia
Sent: Monday, April 05, 2010 11:07 AM
To: mysql@lists.mysql.com
Subject: Slow Union Statement
Hi
Yes, that's the trick. Thank Rudy and Gavin.
Best,
Jia
On Mon, Apr 5, 2010 at 2:13 PM, Gavin Towey gto...@ffn.com wrote:
Union does a distinct on all results. UNION ALL will avoid that.
Regards,
Gavin Towey
-Original Message-
From: chen.1...@gmail.com [mailto:chen.1...@gmail.com
Hi;
I would like to create a table out of merging the fields in other,
previously created tables. I have the following syntax which doesn't work:
create table products union (b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics);
Please advise.
TIA
The form would be like:
CREATE TABLE products
SELECT b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics FROM table1
UNION
SELECT b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics FROM table2
Regards
On Sun, Nov 22, 2009 at 9:29 PM, Gavin Towey gto...@ffn.com wrote:
The form would be like:
CREATE TABLE products
SELECT b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics FROM table1
UNION
SELECT b0basics, b0fieldValues, s0prescriptions
| index_merge | buchungs_kunde_id,kunde_id |
buchungs_kunde_id,kunde_id | 71,71 | NULL |2 | Using
union(buchungs_kunde_id,kunde_id); Using where
|
++-+---+-++-
---+-+--+--+
--+
| 1 | SIMPLE | KTEMP | index_merge | buchungs_kunde_id,kunde_id |
buchungs_kunde_id,kunde_id | 71,71 | NULL |2 | Using
union(buchungs_kunde_id,kunde_id); Using where
-Ursprüngliche Nachricht-
Von: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com]
Gesendet: Dienstag, 13. Oktober 2009 15:26
An: Skoric, Majk
Cc: mysql@lists.mysql.com
Betreff: Re: OR vs UNION
Majk, all,
I'm no optimizer expert, but your result doesn't really surprise me
) representing a user's answer to a
survey.
I'm trying to determine how many surveys per show responded with response_id
30 and response_id 28
I think I need a union here, but am not sure.
So far, I THINK I can figure out how many surveys per show responded with just
response 30 as follows:
SELECT
glenn
Union is 4.0 feature.
david
Notwithstanding end-of-life status, is there an issue in MySQL
v3.23 r.e. the union operator?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Notwithstanding end-of-life status, is there an issue in MySQL v3.23
r.e. the union operator?
My installation returns a syntax error on any query containing the union
operator, even with the sample queries provided in the documentation:
mysql SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10
On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
Notwithstanding end-of-life status, is there an issue in MySQL v3.23
r.e. the union operator?
My installation returns a syntax error on any query containing the union
operator, even with the sample queries provided
Jim Winstead wrote, On 1/11/2008 12:54 PM:
On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
Notwithstanding end-of-life status, is there an issue in MySQL v3.23
r.e. the union operator?
My installation returns a syntax error on any query containing the union
operator, even
Hi all,
I believe to have found a bug in MySQL's union implementation. Can someone
confirm this, please or convince me that this is not a buggy behaviour of
mysql :
UNION seems to behave like DISTINCT by default:
mysql select 2 c1
- union
- select 1 c1
- union
- select 2 c1
UNION will only return distinct rows. This is according to spec and to
the SQL Standard. To avoid this, use UNION ALL instead of UNION. Try
that with your queries and you'll see that this will do the trick. This
is, as I said, in accordance with the standard and the way all SQL based
databases
UNION is mean to removed duplicate rows. Use UNION ALL if you don't want
this to happen.
http://dev.mysql.com/doc/refman/5.0/en/union.html
-Original Message-
From: list account [mailto:[EMAIL PROTECTED]
Sent: 11 July 2007 09:19
To: mysql@lists.mysql.com
Subject: BUG in UNION
On Wednesday 11 July 2007 00:34, Anders Karlsson wrote:
UNION will only return distinct rows. This is according to spec and to
the SQL Standard.
And of course, to no one's surprise, this also matches the mathematical
definition of union:
j
--
Joshua Kugler
Lead
Hi everyone,
I have a couple of questions invovling using FOUND_ROWS() and UNION.
The manual states that:
The value of FOUND_ROWS() is exact only if UNION ALL is used. If
UNION without ALL
is used, duplicate removal occurs and the value of FOUND_ROWS() is
only approximate.
I am using UNION
Hi Luke..
Try this
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')
UNION
SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId
A big Thank you goes to you! That was it!
Looks like I tried with too many parentheses i.e.
/this is wrong/
SELECT FROM ...
(
(SELECT FROM ...)
UNION
(SELECT FROM ...)
) AS abc
Regards,
Luke
- Original Message -
From: Neeraj [EMAIL PROTECTED]
To: 'Luke' [EMAIL PROTECTED
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15
H L [EMAIL PROTECTED] wrote on 04/03/2006 01:53:37 PM:
The solution is to redesign your tables. You need to split into
separate
columns the values you want to maintain. You do not want to keep the
flat
file design you are currently trying to use.
CREATE TABLE calendar (
objectid,
The solution is to redesign your tables. You need to split into separate
columns the values you want to maintain. You do not want to keep the flat
file design you are currently trying to use.
CREATE TABLE calendar (
objectid,
year,
dayofyear,
... other fields...
)
CREATE TABLE price (
Hi, i have a problem to select sum from same table using UNION. The key
question is there a way of combining two questions in one so the resulting
objectid in query1 is only used in query 2 in a smart way. Eg. if only one
object is avaliable in a search first year then only check
H L [EMAIL PROTECTED] wrote on 04/02/2006 11:51:48 AM:
Hi, i have a problem to select sum from same table using UNION. The
key
question is there a way of combining two questions in one so the
resulting
objectid in query1 is only used in query 2 in a smart way. Eg. if only
one
object
Hi,
The following 3 queries on their own produce more rows than if I UNION them
together:
SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('W_', Work_Type_ID) AS Day_Type,
'1
The UNION Syntax is:
SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]
Lower in the manual it says:
* A DISTINCT union can be produced explicitly by using UNION
DISTINCT or implicitly by using UNION with no following DISTINCT or ALLkeyword.
*
**
So
Shaun [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi,
The following 3 queries on their own produce more rows than if I UNION
them
together:
SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID
Hello mysql,
Union on zerofilled fields eats 'zerofilling'.
How to repeat:
create table a (id integer zerofill);
insert into a values(1),(2),(3);
select * from a;
++
| id |
++
| 01 |
| 02 |
| 03 |
++
select * from
Interesting... maybe this is because the fill is not actualy stored in the
database... and being sorted/compared as a number MySQL removes the ZEROFILL
!
You can go and do:
select BINARY * from a
union
select BINARY * from a;
--
Gabriel PREDA
Senior Web Developer
On 2/14/06, Juri Shimon [EMAIL
Hi Gleb,
i finally found out a method to be entirely independent from any
character-set as well as collation-sequence problem, when forming a
UNION, where you occasionnally have to insert place-holders in one of
the SELECT statements:
as (text, varchar, char) placeholders use NULL instead
Hi Gleb,
localhost.(none) show session variables like %version%;
+-+--+
| Variable_name | Value|
+-+--+
|
|
+--+---+
3 rows in set (0.00 sec)
localhost.addresses2
4. following problem:
when i do a union statement between the two tables, where obviousely at
least in one select of the union some fields are left open by the
placeholder or '' , these left-off fields are acted
I have a simple stored procedure which works as intended.
As soon as I add a UNION in the SELECT I get the error message
ERROR 1064 (42000): 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 'select phon_Lvl
| latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)
localhost.addresses2
4. following problem:
when i do a union statement between the two tables, where obviousely at
least in one select of the union some
sending 50 insert into summarytable select * from table-xx
on duplicate key update commands.
After explaining my details, I would like to know, if there is a
possibility of a command like
insert into summarytable
select * from `table-01` union select * from `table-02` union
select * from `table
Hi,
I am trying to run an sql query such as below to list items=x randomly and
then items != x randomly..
mysql (select * from tablea where item=1 order by rand()) union all
(select * from tablea where item != 1 order by rand());
but the result is not as expected. rand() seems
for your query.
HALIL DEMIREZEN wrote:
Hi,
I am trying to run an sql query such as below to list items=x randomly and
then items != x randomly..
mysql (select * from tablea where item=1 order by rand()) union all
(select * from tablea where item != 1 order by rand
]
---
Gleb Paharenko [EMAIL PROTECTED]
05.12.2005 12:19
To
mysql@lists.mysql.com
cc
Subject
Re: About union sql Mysql 4.x
Hello.
From http://dev.mysql.com/doc/refman/5.0/en/union.html:
ORDER BY for individual SELECT statements within parentheses
HALIL DEMIREZEN wrote:
Hi,
I am trying to run an sql query such as below to list items=x randomly and
then items != x randomly..
mysql (select * from tablea where item=1 order by rand()) union all
(select * from tablea where item != 1 order by rand());
but the result is not as expected
Michael,
Thank you and all for effort to help.. I solved the problem by giving high
limit numbers such as;
(select * from tablea where item=1 order by rand() limit 0, 1)
union all
(select * from tablea where item != 1 order by rand() limit 0,
1
[EMAIL PROTECTED]
05.12.2005 12:19
To
mysql@lists.mysql.com
cc
Subject
Re: About union sql Mysql 4.x
Hello.
From http://dev.mysql.com/doc/refman/5.0/en/union.html:
ORDER BY for individual SELECT statements within parentheses has
HALIL DEMIREZEN wrote:
Michael,
Thank you and all for effort to help.. I solved the problem by giving high
limit numbers such as;
(select * from tablea where item=1 order by rand() limit 0, 1)
union all
(select * from tablea where item != 1 order by rand() limit 0,
1
I'm trying to run this :
SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID =
B.U_Number and B.U_Username = 'user1'
UNION
SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID =
D.U_Number and D.U_Username = 'user2'
and it's failing saying
SQL Error
Simon Longstaff [EMAIL PROTECTED] wrote on
10/28/2005 10:50:24 AM:
I'm trying to run this :
SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID
=
B.U_Number and B.U_Username = 'user1'
UNION
SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID
Hello.
MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE)
and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again
Check the character set of fields in you tables with
'SHOW CREATE TABLE' statement. I recommend you to test if
the problem remains in 4.1.14
Hi list,
I've got a query coming out of sugarCRM that is generating this error:
MySQL error 1267: Illegal mix of collations
(latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for
operation 'UNION'
I recently converted the entire database to utf8 - made sure all the
connections
I have a table that holds attributes for users. This is the structure:
TABLE properties (
id int(11) NOT NULL,
userid int(11) NOT NULL,
attrType int(11) NOT NULL,
attrValue text NOT NULL,
FULLTEXT KEY propValue (propValue)
) TYPE=MyISAM;
The table is used to find people based on
It's getting late on Friday, but couldn't you build a table with all of
the parameter combinations and then just join against that table?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs
: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs OR
I have a table that holds attributes for users. This is the structure:
TABLE properties (
id int(11) NOT NULL,
userid int(11) NOT NULL,
attrType int(11) NOT NULL,
attrValue text NOT NULL,
FULLTEXT KEY
Hello.
What is the value of the default_charset variable in your php.ini file?
What version of MySQL do you use?
Florian Burkart [EMAIL PROTECTED] wrote:
What might help as well is another problem I have:
Somehow, the data I am getting out of mysql and php and is being served by
Hey Gleb,
thanks for taking your time!
php.ini:
; As of 4.0b4, PHP always outputs a character encoding by default in
; the Content-type: header. To disable sending of the charset, simply
; set it to be empty.
;
; PHP's built-in default is text/html
default_mimetype = text/html
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message
.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations
] wrote:
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER
CREATE TABLE tbl_gruppen;
Florian Burkart [EMAIL PROTECTED] wrote:
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar
I have a UNION whose statements when interchanged gives a different result. I
can understand the change in the order of the rows, but how is it that
'picture' gets the correct value in (a) but not in (b)? Here are the two
queries and their results:
(a)
(SELECT u.lName last_name, u.picture
From http://dev.mysql.com/doc/mysql/en/union.html :
Before MySQL 4.1.1, a limitation of UNION is that only the values
from the first SELECT are used to determine result column types and
lengths. This could result in value truncation if, for example, the
first SELECT retrieves shorter values than
`a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2
WHERE a='a') ORDER BY id LIMIT 0,5 \G
*** 1. row ***
id: 1
select_type: PRIMARY
table: t1
I am not sure about index merging but you should be able to speed things
up if you ORDER BY and LIMIT your inner queries as well:
(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5)
UNION
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5)
ORDER BY id LIMIT 0,5
To answer your UNION query
:-)
[EMAIL PROTECTED] wrote:
I am not sure about index merging but you should be able to speed
things up if you ORDER BY and LIMIT your inner queries as well:
(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5)
UNION
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5)
ORDER BY id LIMIT 0,5
Hello.
MySQL uses indexes in queries which are parts of UNION. See:
mysql explain (select * from t1 where a=5) union (select * from t1 where
a=3)\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: t1
type
Hello,
MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION
queries? If yes, for which version (assumed release time)?
Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system
Hello.
Check with SHOW PROCESSLIST in which state MySQL thread which performs
the query is. See:
http://dev.mysql.com/doc/mysql/en/show-processlist.html
Chris Faulkner [EMAIL PROTECTED] wrote:
Hello again
Following on from this index question, the UNION worked. From a normal
Hello again
Following on from this index question, the UNION worked. From a normal
mysql client, it was returning my results sub-second. I am actually
executing this over JDBC, using mysql-connector j.
WHen I put the SQL into my Java program - it takes a minute or so. I
am logging the SQL
Hello.
I've searched in the archives at:
http://lists.mysql.com/mysql
but the only thing I've found is that the speed difference is rather low. In my
opinion, using UNION is a more flexible solution.
pow [EMAIL PROTECTED] wrote:
Hi everyone,
Take for example two identical
Hi everyone,
Take for example two identical tables A B, and a MERGE table merging
both of them.
Would there be any performance advantage if I do a select (with a where
criteria on an indexed column) on the MERGE table, as opposed to doing a
union of two selects with the same WHERE criteria
` ,
`text`
);
INSERT INTO searchTemp
(
SELECT 'content', id, headline, text, teaser, image_id FROM content
)
UNION ALL
(
SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline,
Beskrivelse AS text, '', image_id FROM wesHoldKategori
Dennis Duggen wrote:
Hi list
Im new to the list so i hope it's the right place for my post.
For a projekt i am combining different tables. Everything seems to work,
but in some rows the Kode field VARCHAR(10) ends up as INT. I will try
to explain with an example.
Table1
46
47
48
67
Table2
' results should have the same type.
Try to cnage order of SELECTs in your query.. I think you'll get what
you want.
Right, the type of each column is determined by the first SELECT in the UNION.
Hence, your VARCHARs are converted to INTs because they are in an INT column
based on the first SELECT
Here's a big problem I'm having.
If I have a query like:
SELECT * FROM FOO WHERE FOO.LAST_UPDATED 1119898418779 AND
FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10
it only takes about 10ms or so to execute.
but... if I rewrite it to wrap it in a union like so:
( SELECT * FROM FOO
SQL expression is evaluated which is then given to the union. Evil I
say! Pure evil!
I was able to figure this out because Handler_read_next was being
incremented to the same value as the total number of rows in this
expression.
Cool now at least I know why its screwing up.
Kevin
Why do you think you're using a UNION in your query? The keyword UNION
doesn't appear anywhere in your query. You don't even have a second query
being UNIONed to the first. All you've got is a pair of parentheses
surrounding your original query, which seems to perform okay.
For what it's worth, I
Kevin Burton wrote:
Any idea whats going on and how I could fix this?
This seems like a bug in the SQL parser. The LIMIT is only ignored in this one
situation.
If I just add a:
UNION
(SELECT * FROM FOO LIMIT 0)
To the query will work correctly.
This might be an acceptable workaround
as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where
[EMAIL PROTECTED];
+--+--+---+
| id | selected | type
query.
The only condition is to add a where clause a the column type which will
retreive empty set for the non selected conditions.
Example :!
set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select
The second method is dynamic sql with prepare statement from string.
It's better for the reason that the query is generated to retrieve data from
just one table (not an union which implies 3 tables).
The day your tables will be huge, i'm sure you will use the second method.
two crazy people can
Sebastian wrote:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which works just fine and selects
, and other rdbms.
P.S. haven't spent days to help. It is easy. And since it was a week-end :o)
Mathias
Selon Michael Stassen [EMAIL PROTECTED]:
Sebastian wrote:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM
Michael Stassen wrote:
Sebastian wrote:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
[EMAIL PROTECTED] wrote:
I agree with you,
just see that if ..
sql = SELECT id, '$cat' AS type, ... FROM $cat;
is exactly what is done in prepare statement.
Yes, I acknowledged that in my next message (a reply to yours).
dynamic sql is better than application level statement preparation,
Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type
the one-statement-in-sql version I sent in my
first reply to Mathias' suggestions. It's just a matter of adding 2
conditions to each WHERE clause in the UNION, so I think it will meet
your specs, even for your real query.
Michael
--
MySQL General Mailing List
For list archives: http
')
UNION
SELECT id, 'faq' AS type, FROM faq
WHERE ($cat = '' OR $cat = 'faq')
UNION
SELECT id, 'forum' AS type, FROM forum
WHERE ($cat = '' OR $cat = 'forum')
snip
Michael
you just gave me an idea.. maybe this is better:
SET @cat = '$cat
= '' OR $cat = 'news')
UNION
SELECT id, 'faq' AS type, FROM faq
WHERE ($cat = '' OR $cat = 'faq')
UNION
SELECT id, 'forum' AS type, FROM forum
WHERE ($cat = '' OR $cat = 'forum')
snip
Michael
you just gave me an idea.. maybe
Sebastian wrote:
Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat
clause be present first in the where clause? since it decides if it
should get results or not. for example:
WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which works just fine and selects everything from all 3 tables.. but say
i want to make
1 - 100 of 334 matches
Mail list logo
