`;
Table `t3` won't have any indices, even if table `t2` does, is that correct?
(Assume that the sub-select is really much more complicated that my
example.) Wouldn't it be a lot faster to replace the sub-select with a
temporary table that does have a key on `y`?
Regards,
Jerry Schwartz
Hi list,
does MySQL accept a SELECT inside another SELECT, like this:
SELECT t.Trial_ID_Code AS Trial,
CONCAT(s.Site_Primary_Investigator_Title, ,
s.Site_Primary_Investigator_Last_Name) AS 'Investigator',
CONCAT(sc.Site_ID_Number, -, s.Site_ID_Code, -, sc.Scanner_ID_Number)
AS Site,
Hi
Here's a complicated bit sql that I'm trying to Post from MSAccess to
MySql
I searched UseNet and read that in MySQL you cannot have a sub-select,
but I'm lost as to how to split this into two selects?
Any help gratefully received!!!
SELECT distinct p.ProjectID ,p.ProjectName FROM
I am using version 3.23.58 and need to do
the following:
select * from a where id not in (select tbl_id from b where tbl=a);
Given that my version does not support sub selects, how
can I re-write the statement to get the desired results?
Thanks.
Dean Hoover
--
MySQL General Mailing List
For list
[mailto:[EMAIL PROTECTED]
Sent: Friday, December 12, 2003 2:04 PM
To: [EMAIL PROTECTED]
Subject: sub select equivalent
I am using version 3.23.58 and need to do
the following:
select * from a where id not in (select tbl_id from b where tbl=a);
Given that my version does not support sub
At 14:03 -0500 12/12/03, Dean A. Hoover wrote:
I am using version 3.23.58 and need to do
the following:
select * from a where id not in (select tbl_id from b where tbl=a);
Given that my version does not support sub selects, how
can I re-write the statement to get the desired results?
Mauro Andre Flores dos Santos [EMAIL PROTECTED] wrote:
I've just downloaded the version 4.1.
I tryed to run my first sub-select :
SELECT curso.*
FROM curso
where curso.cdCurso IN
(SELECT EstabMantemCurso.cdCurso
FROM EstabMantemCurso)
The following ERROR returned:
[local] ERROR
Hi,
I've just downloaded the version 4.1.
I tryed to run my first sub-select :
SELECT curso.*
FROM curso
where curso.cdCurso IN
(SELECT EstabMantemCurso.cdCurso
FROM EstabMantemCurso)
The following ERROR returned:
[local] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT
Hi,
I am running MySQL 4.0.10 gamma.
I have records with data:
id title version
1a 1
2a 2
3b 1
4b 2
How do I select all the records with highest version for each title?
(The records with id 2 and 4)
I've been thinking about combinating GROUP BY and
Hi Ola,
How about SELECT id, title, max(version) FROM table GROUP BY version ORDER BY
title; ?
Thats a guess though...
Best regards
Nils Valentin
Tokyo/Japan
2003 6 5 22:[EMAIL PROTECTED] :
Hi,
I am running MySQL 4.0.10 gamma.
I have records with data:
id title version
1a
[EMAIL PROTECTED] wrote:
Hi,
I am running MySQL 4.0.10 gamma.
I have records with data:
id title version
1a 1
2a 2
3b 1
4b 2
How do I select all the records with highest version for each title?
(The records with id 2 and 4)
I've been thinking
[EMAIL PROTECTED] schrieb:
I am running MySQL 4.0.10 gamma.
I have records with data:
id title version
1a 1
2a 2
3b 1
4b 2
How do I select all the records with highest version for each title?
[...]
Try:
SELECT t1.id, t1.title, t1.version
FROM
Thanks everyone!
Here is a working solution:
(Normalizing the tables as O'K Web Design suggested is also a good design
solution)
Hans-Peter Grimm [EMAIL PROTECTED]
[EMAIL PROTECTED] schrieb:
I am running MySQL 4.0.10 gamma.
I have records with data:
id title version
1a 1
2
Description:
MySQL doesn't allow me to use a sub-select in an update if
the subselect happens to be from the same table.
How-To-Repeat:
mysql use test;
mysql create table T(lfd int, foo int);
mysql insert into T (lfd, foo) values ( ( select max( lfd ) + 1
Hello friends!
The MySQL-4.0.9-gamma to windows supports sub select?
Ex.: select ... from ... where ... in (select.)
If not, has some version that supports it?
Thanks,
e.
_
MSN Hotmail, o maior webmail do Brasil
Elby,
The MySQL-4.0.9-gamma to windows supports sub select?
Nope. Subselect are supported as of 4.1.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
In the meantime, you can almost always perform the equivalent of a
sub-select using an inner join (and sometimes a temp table).
For example, a query to select all customers in the province of Ontario:
SELECT * FROM Customers
WHERE CityID IN( SELECT CityID FROM Cities WHERE ProvinceID = ON )
Can
On Thursday 13 February 2003 15:03, Elby Vaz wrote:
The MySQL-4.0.9-gamma to windows supports sub select?
Nope.
Ex.: select ... from ... where ... in (select.)
If not, has some version that supports it?
Subselects will come in 4.1
--
For technical support contracts, goto https
hi guys i was wondering if sub selects were possible in mysql 4 ? more
specifically , whenever i do a join , i sometimes require a distinct select on
the field in the first table and the second table may have many rows therefore
returns the same value more than once from the first table which
On Wed, Jan 08, 2003 at 07:05:41PM -0800, Sal wrote:
Hi,
I'm using MySQL 4.0.7 gamma version. I just want to
know if MySQL support sub select statement. The
example of sub select statement is as below. I've
tried many times but then get an error.
No. They are in 4.1.0.
--
Jeremy D
Dear Jeremy,
Thaks 4 ur info. Where can I get MySQL 4.1.0.
regards,
Sal
--- Jeremy Zawodny [EMAIL PROTECTED] wrote:
On Wed, Jan 08, 2003 at 07:05:41PM -0800, Sal wrote:
Hi,
I'm using MySQL 4.0.7 gamma version. I just want
to
know if MySQL support sub select statement. The
example
On Thursday 09 January 2003 09:32, Sal wrote:
Thaks 4 ur info. Where can I get MySQL 4.1.0.
You can compile it from development source tree:
http://www.mysql.com/doc/en/Installing_source_tree.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This
Hi,
I'm using MySQL 4.0.7 gamma version. I just want to
know if MySQL support sub select statement. The
example of sub select statement is as below. I've
tried many times but then get an error.
Example:
select alc, fdate, sum(totalpax) from (select distinct
alc, fln, fdate, totalpax from
Dear all,
I have the following query. When executed in MySQL I get an error on the
first sub-select, and I can't find why:
select slbc.stockcode, s.description, slp.qtyinstock, smax.pluqty,
(slp.qtyinstock / smax.pluqty)
from stocklevelbc slbc, stock s, stocklevel slp, stock smax
I have the following query. When executed in MySQL I get an error on the
first sub-select, and I can't find why:
http://www.mysql.com/doc/en/News-4.1.x.html
You'll see that sub-select query will be in future 4.1.xx release ...
David
Table2.ID IS NULL
K because of MySQL ver 3.23.XX not support sub select while we
still
K use 3.23.XX
In 3.23. you can't do it with single SQL statement. You can use
programming language or use CONCAT() function as described here:
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
KW Yes I use
that use to delete the record in Table1 that not
found
K in Table2 such as
K Delete from Table1 where Table1.ID in
K SELECT Table1.ID FROM Table1 LEFT JOIN Table2 Using(ID)
K WHERE Table2.ID IS NULL
K because of MySQL ver 3.23.XX not support sub select while we
still
K use 3.23.XX
In 3.23. you
in Table2 such as
K Delete from Table1 where Table1.ID in
K SELECT Table1.ID FROM Table1 LEFT JOIN Table2 Using(ID)
K WHERE Table2.ID IS NULL
K because of MySQL ver 3.23.XX not support sub select while we still
K use 3.23.XX
In 3.23. you can't do it with single SQL statement. You can use
programming
.ID FROM Table1 LEFT JOIN Table2 Using(ID)
WHERE Table2.ID IS NULL
because of MySQL ver 3.23.XX not support sub select while we still
use 3.23.XX
and one more question
How to merge table with varchar type not char type (because if table
contain varchar column we can't merge them but with table
-like thingie) Re:
Sub-select look-alike?
Hi Bill,
you wrote to me and probably several other people:
If I understand your question, you just need to join with the languages
table twice, using aliases:
select LF.language, LT.language
from language_pairs P, languages LF, languages
= location.location;
(the foreign key reference is on a third table, the category table, but I can only get
there through
the location table, the one with the nulls)
it would seem to call for some kind of array/loop insert, how can I do that without
using a sub-select?
i can't do a insert
Hi,
I have two tables:
languages:
id tinyint(3) unsigned not null,
language varchar(30) not null
language_pairs:
from tinyint(3) unsigned not null,
to tinyint(3) unsigned not null
language_pairs.from and language_pairs.to are linked with
: Sub-select look-alike?
Hi,
I have two tables:
languages:
id tinyint(3) unsigned not null,
language varchar(30) not null
language_pairs:
from tinyint(3) unsigned not null,
to tinyint(3) unsigned not null
language_pairs.from and language_pairs.to
13, 2002 10:13 AM
To: [EMAIL PROTECTED]
Subject: Sub-select look-alike?
Hi,
I have two tables:
languages:
id tinyint(3) unsigned not null,
language varchar(30) not null
language_pairs:
from tinyint(3) unsigned not null,
to tinyint(3) unsigned not null
Hi Greg,
SELECT f.language as From, t.language as To
FROM language f, language t, language_pairs lp
WHERE f.id = lp.from
AND t.id = lp.to;
I really need to get some sleep I think.
So simple, and yet my buggy mind didn't come up with that solution.
Thanks,
//andreas
(sql, query - just to
]
To: [EMAIL PROTECTED]
Subject: Sub-select look-alike?
Date: Wed, 13 Mar 2002 16:12:31 +0100
Hi,
I have two tables:
languages:
id tinyint(3) unsigned not null,
language varchar(30) not null
language_pairs:
from tinyint(3) unsigned not null
the select returns. The restriction is less relevant if you are performing a
select which can return at most one row, but mySQL still enforces it.
There are a couple of solutions to this. The cleanest one is probably just
to use temporary tables to implement a true sub-select. (I've put together
enforces it.
There are a couple of solutions to this. The cleanest one is probably just
to use temporary tables to implement a true sub-select. (I've put together a
framework to do this hidden behind an abstraction layer so that I can do
subselects whether I'm using mySQL or a different database
enforces it.
There are a couple of solutions to this. The cleanest one is probably just
to use temporary tables to implement a true sub-select. (I've put together a
framework to do this hidden behind an abstraction layer so that I can do
subselects whether I'm using mySQL or a different database
Hi,
MySQL has insert into function and sub select (mysql style) but I
could not conclude these function togethter.
If I want to select not existing row in 2 table, I used:
SELECT table1.* FROM table1 LEFT JOIN table2 ON
table1.id=table2.id where table2.id is null
At 9:18 +0700 2/27/02, Sommai Fongnamthip wrote:
Hi,
MySQL has insert into function and sub select (mysql style)
but I could not conclude these function togethter.
If I want to select not existing row in 2 table, I used:
SELECT table1.* FROM table1 LEFT JOIN table2
I was successful to get result from select (this sub-select appear in MySQL
manual).
At 21:20 26/2/2002 -0600, Paul DuBois wrote:
At 9:18 +0700 2/27/02, Sommai Fongnamthip wrote:
Hi,
MySQL has insert into function and sub select (mysql style) but
I could not conclude these function
Hi,
I know sub select would not work correct in 3.23.xx but I have a question
to ask. I'd like to find top 10 ranking of my customer by amount (using
order clause desc) but there are some customer have the same amount. I was
solve this problem by using group by and insert
Hello
Can I make a query in mySQL like this?
DELETE FROM table1
WHERE id NOT EXIST IN (SELECT id FROM table2)
--
WBR
Lyubomir mailto:[EMAIL PROTECTED]
-
Before posting, please
no
-Original Message-
From: Lyubomir Simich [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 12:04 PM
To: [EMAIL PROTECTED]
Subject: SUB-SELECT
Importance: High
Hello
Can I make a query in mySQL like this?
DELETE FROM table1
WHERE id NOT EXIST IN (SELECT
You can'd use sub-selects... you have to do it with a join.
Chris
Can I make a query in mySQL like this? DELETE FROM table1 WHERE id NOT
EXIST IN (SELECT id FROM table2)
-
Before posting, please check:
they will be in mysql 4.1
-Original Message-
From: Rosyna [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 2:11 PM
To: Christopher Book; '[EMAIL PROTECTED]'
Subject: Re: SUB-SELECT
Anyone know if subselects will be available in the future?
Ack, at 11/15/01, Christopher
so what's the remedy?
how to do this task in MySQL without using subselects?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Trond Eivind Glomsrød
Sent: Monday, October 22, 2001 1:13 PM
To: Moshe Gurvich
Cc: [EMAIL PROTECTED]
Subject: Re: how to sub
Trond Eivind Glomsrød wrote:
Moshe Gurvich [EMAIL PROTECTED] writes:
I'm trying to run:
delete from followups where task_id not in (select task_id from tasks)
but it gives me an error:
Error: 1064 - You have an error in your SQL syntax near 'select task_id from
tasks)' at line 1
Hi!
On Oct 22, Moshe Gurvich wrote:
so what's the remedy?
how to do this task in MySQL without using subselects?
delete from followups where task_id not in (select task_id from tasks)
Multi-table deletes (MySQL 4.0.0+)
DELETE followups FROM followups LEFT JOIN tasks USING(task_id)
:)
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 1:57 PM
To: Moshe Gurvich
Cc: [EMAIL PROTECTED]
Subject: Re: how to sub-select?..
Hi!
On Oct 22, Moshe Gurvich wrote:
so what's the remedy?
how to do this task in MySQL without using
Hi!
On Oct 22, Moshe Gurvich wrote:
thank you very much;
i'm using right now v3.23 as recommended for production servers, but
anyway..
now, using the way you suggested, will this work:
DELETE FROM tasks
FROM tasks as t_child LEFT JOIN tasks as t_parent ON
in JOIN structure?
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 3:01 PM
To: Moshe Gurvich
Cc: [EMAIL PROTECTED]
Subject: Re: how to sub-select?..
Hi!
On Oct 22, Moshe Gurvich wrote:
thank you very much;
i'm using right now v3.23
Hi!
On Oct 22, Moshe Gurvich wrote:
yeah, noticed it after clicked send..
let's say i upgrade to MySQL 4.0 and run this query:
DELETE FROM tasks as t_child LEFT JOIN tasks as t_parent ON
t_child.parent_id=t_parent.task_id
WHERE t_parent.task_id IS NULL
will this work?
no.
=t_parent.task_id
WHERE t_parent.task_id IS NULL
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 3:52 PM
To: Moshe Gurvich
Cc: [EMAIL PROTECTED]
Subject: Re: how to sub-select?..
Hi!
On Oct 22, Moshe Gurvich wrote:
yeah, noticed it after
On 07 Aug 2001 10:46:31 -0400, Trond Eivind Glomsrød wrote:
MySQL doesn't have subselects. Some workarounds can be found at
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Missing_Sub-selects
PostgreSQL supports them.
Actually MySQL 4.0 will do.
--
For
Den 01-08-07 14.57, skrev Anders Alstrin [EMAIL PROTECTED]:
Does anyone know how to do this sub select in MySQL.
I know how to do it in Oracle!
table media
+--+---+--+
| media_id | price |filename |
+--+---+--+
|3 | 0 | cal.jpg
I don't think MySQL supports sub selects.
Tyler
On Tue, 07 Aug 2001 14:57:33 +0200
Anders Alstrin [EMAIL PROTECTED] wrote:
Does anyone know how to do this sub select in MySQL.
I know how to do it in Oracle!
table media
+--+---+--+
| media_id | price |filename
= 'true'
-Original Message-
From: Anders Alstrin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 07, 2001 2:58 PM
To: [EMAIL PROTECTED]
Subject: Sub select.
Does anyone know how to do this sub select in MySQL.
I know how to do it in Oracle!
table media
Anders Alstrin [EMAIL PROTECTED] writes:
Does anyone know how to do this sub select in MySQL.
MySQL doesn't have subselects. Some workarounds can be found at
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Missing_Sub-selects
PostgreSQL supports them.
--
Trond
On 07 Aug 2001 14:57:33 +0200, Anders Alstrin wrote:
SELECT * FROM media
WHERE media_id = (SELECT media_id FROM category_locks
WHERE category_id = (SELECT category_id FROM categories
WHERE dynamic_media = 'true'))
A MySQL way to do it?
Something like that should work:
SELECT * FROM
:[EMAIL PROTECTED]]
Sent: Tuesday, August 07, 2001 9:58 AM
To: Anders Alstrin
Cc: [EMAIL PROTECTED]
Subject:Re: Sub select.
I don't think MySQL supports sub selects.
Tyler
On Tue, 07 Aug 2001 14:57:33 +0200
Anders Alstrin [EMAIL PROTECTED] wrote:
Does anyone know how to do
# Gustav-Meyer-Allee 25, 13355 Berlin
# Tel: +49-30-46307-382 Fax: +49-30-46307-388
- Original Message -
From: Anders Alstrin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 07, 2001 2:57 PM
Subject: Sub select.
Does anyone know how to do this sub select in MySQL.
I know
63 matches
Mail list logo
