Re: subselect logic

2008-06-25 Thread Kip Turk
Awesome, thanks. My first attempt is able to run on the full table in 85 seconds. I'll continue to read up on these queries to see if I can optimize it further. Thanks again. Peter Brawley wrote: Kip, > What can I do to optimize this query? For more efficient alternatives see "Within-gr

Re: subselect logic

2008-06-25 Thread Peter Brawley
Kip, > What can I do to optimize this query? For more efficient alternatives see "Within-group aggregates" at http://www.artfulsoftware.com/queries.php. PB - Kip Turk wrote: I'm having problems optimizing a series of subselects. I have the following sample table: mysql> select * fr

Re: Subselect application

2006-05-05 Thread sheeri kritzer
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ Is a good article. -Sheeri On 4/21/06, Chris White <[EMAIL PROTECTED]> wrote: I was looking around the list search and didn't find much on this subject (maybe didn't look back far enough), but I was discussing

Re: Subselect in an Update query

2005-06-18 Thread Jochem van Dieten
On 6/17/05, [EMAIL PROTECTED] wrote: > > There is one caveat: It is not currently possible to modify a table and select > from the same table in a subquery. That is not the only problem: there is no guarantee the subquery will only return one record. So even if MySQL wouldn't have this limitation

Re: Subselect in an Update query

2005-06-17 Thread Ed Reed
Thanks for the reply. What do you mean by 'self join'? >>> Eric Bergen <[EMAIL PROTECTED]> 6/17/05 4:39:28 PM >>> Could you accomplish this with an update and self join? Ed Reed wrote: >Sorry, I thought it was easy to understand. > >I wanna update a field in a table with a value from the s

Re: Subselect in an Update query

2005-06-17 Thread Ed Reed
Thanks >>> <[EMAIL PROTECTED]> 6/17/05 2:03:02 PM >>> Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as S

Re: Subselect in an Update query

2005-06-17 Thread Eric Bergen
Could you accomplish this with an update and self join? Ed Reed wrote: Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +

Re: Subselect in an Update query

2005-06-17 Thread Ed Reed
Sorry, I thought it was easy to understand. I wanna update a field in a table with a value from the same field but from a different record of the same table. For example, using the query in my original message, +--+ |RecID |FIELD1 |FIELD2 | +---

Re: Subselect in an Update query

2005-06-17 Thread mfatene
Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as Select field1 From table1 Where field2="Some Value" Upda

Re: Subselect in an Update query

2005-06-17 Thread SGreen
"Ed Reed" <[EMAIL PROTECTED]> wrote on 06/17/2005 01:35:40 PM: > Can anyone tell me how I can make this work or suggest a work around? > Update table1 Set field1=(Select field1 From table1 Where field2="Some Value") > Where field2 ="Another Value"; > Thanks OK, your example must be broken (or

Re: Subselect in aggregate function in MySQL 4.1.1a-alpha

2004-02-21 Thread Bernhard Döbler
rning multiple rows in only one col to an aggregate function. Thanks and bye, Bernhard - Original Message - From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 19, 2004 2:34 PM Subject: Re: Subselect in aggregate function in My

Re: Subselect in aggregate function in MySQL 4.1.1a-alpha

2004-02-19 Thread Victoria Reznichenko
[EMAIL PROTECTED] wrote: > > Hi, > > I have some logging data from a webserver in a table and want to do some > analysis. > I infact want to see how many files are requested at one time. > To do this I > SELECT COUNT(time) anz FROM table GROUP BY time ORDER BY anz DESC > This gives me the number

Re: Subselect Problem

2003-11-17 Thread Paul DuBois
At 2:28 PM -0500 11/17/03, Peter J. Krawetzky wrote: Could someone please tell me what is wrong with this query: select * from personnel where dept_id not in (select dept_id from attendance_respond) I keep getting a 1024 error message. It looks pretty straightforward to me. I am running 4.0.16 o

Re: SUBSELECT QUESTION?

2003-10-24 Thread Mojtaba Faridzad
First of all, you query is not correct and this is not a subselect query. you can try this: SELECT Salary, Level, Department FROM SalaryInfo ORDER BY Salary DESC LIMIT 1; second of all, for subselect MySQL 4.1 can help you. Mojtaba - Original Message - From: "Prashant Pai" <[EMAIL PROT

Re: Subselect in 4.0.12-max with --new option / Max Date

2003-07-23 Thread Michael Satterwhite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 22 July 2003 22:28, [EMAIL PROTECTED] wrote: > Sorry I completely forget the query: > SELECT * FROM Kardex WHERE Fecha IN (SELECT maxfecha FROM fechas) > P.D. Im using 4.1 alpha version for win32 OK, something is still missing. I went to

RE: Subselect in 4.0.12-max with --new option / Max Date

2003-07-22 Thread mysql
- From: Michael Satterwhite [mailto:[EMAIL PROTECTED] Sent: Martes, 22 de Julio de 2003 06:05 a.m. To: [EMAIL PROTECTED] Subject: Re: Subselect in 4.0.12-max with --new option / Max Date -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 22 July 2003 00:04, [EMAIL PROTECTED] wrote: >

Re: Subselect in 4.0.12-max with --new option / Max Date

2003-07-22 Thread Michael Satterwhite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 22 July 2003 00:04, [EMAIL PROTECTED] wrote: > You are right above the max date query and I really appreciate your > help, but the subselect query its other question. I try the subselect > query with the 4.0.12-max win32 with the --new optio

RE: Subselect in 4.0.12-max with --new option / Max Date

2003-07-21 Thread Paul DuBois
At 1:04 -0400 7/22/03, [EMAIL PROTECTED] wrote: You are right above the max date query and I really appreciate your help, but the subselect query its other question. I try the subselect query with the 4.0.12-max win32 with the --new option in the command line to start the server, according to the m

RE: Subselect in 4.0.12-max with --new option / Max Date

2003-07-21 Thread mysql
feature? I missing something about the conf?? maybe my.ini?? -Original Message- From: Michael Satterwhite [mailto:[EMAIL PROTECTED] Sent: Lunes, 21 de Julio de 2003 10:54 p.m. To: [EMAIL PROTECTED] Subject: Re: Subselect in 4.0.12-max with --new option / Max Date -BEGIN PGP SIGNED MESSAGE

Re: Subselect in 4.0.12-max with --new option / Max Date

2003-07-21 Thread Michael Satterwhite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 21 July 2003 18:48, [EMAIL PROTECTED] wrote: > Cod Product date > 10Nissan Car 2003-06-20 > 20Guitar Yamaha 2003-07-01 > 10Nissan Car 2003-05-01 > 30Stereo Cables 2003-01-20 > 20Gui

RE: Subselect in 4.0.12-max with --new option / Max Date

2003-07-21 Thread mysql
product, MAX(date) FROM table GROUP BY cod And other combination using the HAVING syntax. Please help!! -Original Message- From: Michael Satterwhite [mailto:[EMAIL PROTECTED] Sent: Sábado, 19 de Julio de 2003 08:36 p.m. To: [EMAIL PROTECTED] Subject: Re: Subselect in 4.0.12-max with --new

Re: Subselect in 4.0.12-max with --new option

2003-07-21 Thread Victoria Reznichenko
[EMAIL PROTECTED] wrote: > > I was reading the manual and it said that the subselect is only > available in 4.1 or using the 4.0.12 with the mysqld ?new command line > to start it. > > But it doesn?t working!! So I downloaded the 4.1 alpha version with the > same problem. The error is: > > ERROR

Re: Subselect in 4.0.12-max with --new option

2003-07-19 Thread Michael Satterwhite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 18 July 2003 21:28, [EMAIL PROTECTED] wrote: > 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 …. > > Any ideas? I need subselect working.

Re: subselect question... shouldn't this work?

2003-07-17 Thread Victoria Reznichenko
Ben Margolin <[EMAIL PROTECTED]> wrote: > > I am new to subselects, and what I really want is to do them in an update, > but the following shows a simpler select, that also doesn't seem to work as I > think it should. Advice? Do I just misunderstand how subselects are actually > executed? > > (Th

RE: subselect question... shouldn't this work?

2003-07-16 Thread Rudy Metzger
These are not real subselects. You speak of a subselect when you are define a select in the FROM clause of a kwiri. If you define them in the WHERE clause, you speak of derived tables. Putting them even into the SELECT clause is something specific to MySQL (as far as I know). Ok, now this is theor

Re: Subselect functionality

2003-06-25 Thread Nils Valentin
Thanks Mike for the response, I didnt even think for a moment that CREATE... SELECT, INSERT ...SELECT etc. wouldnt be a subselect command (also it is a nested query). I dont know why I didnt think about that. Mike you mentioned a very good point. Maybe I was thinking to focused to see the whol

RE: Subselect functionality

2003-06-25 Thread Mike Hillyer
I am not sure that could be viewed as a subselect, as it in not a SELECT within an SELECT, but is instead a SELECT within a CREATE. I would imagine that the SELECT within the CREATE is easier to implement that the actual SELECT within a SELECT. Regards, Mike Hillyer www.vbmysql.com > -Origin

Re: Subselect functionality

2003-06-25 Thread Nils Valentin
Hi Egor, Thank you for the reply. Do you mind to go abit mor into details ? It seems that I got domething wrong here. If nested queries are not only subselects, which other nested queries exist there ? Do you mind just writing a few samples, please ? Sorry if this seems a simple question - fr

Re: Subselect functionality

2003-06-25 Thread Egor Egorov
"Nils Valentin" <[EMAIL PROTECTED]> wrote: > > I have a question regarding the MySQL feaures. > > From Version 4.1 Full subselect support was/is announced. > > However if I understood correctly then already from Version3.23-41 (or > earlier) there are some subselect functions already included.

Re: subselect

2003-03-18 Thread Paul DuBois
At 21:34 -0800 3/17/03, geeta varu wrote: does subselect work in mySQL 3.23 ...i'm trying to exceute the following query ... No subselects until 4.1. Perhaps you can rewrite your query as a join. http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html Select can_ID from TABLE1 where (designation

re: Re: subselect

2003-02-21 Thread Victoria Reznichenko
On Friday 21 February 2003 09:16, kk wrote: > You may have to wait until version 4.1 if I am not mistaken. You are not mistaken. Subselects will come in 4.1 > > - Original Message - > From: "geeta varu" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, February 21, 2003 4:09

Re: subselect

2003-02-20 Thread kk
You may have to wait until version 4.1 if I am not mistaken. - Original Message - From: "geeta varu" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 4:09 PM Subject: subselect > does mysql version 3.23 support subqueries ...if no > does latest version 4.0 sup

Re: subselect workaround help?

2003-01-08 Thread Scott Pippin
>Student | Class >-- >Steve Lefevre | Math101 >Stacy Adams | Intro to SQL >Something like >SELECT Student.Name, Classes.Name FROM Students, Classes WHERE >Students.StudentID = " . $ID . " AND Classes.Name IN ( SELECT Classes.Name >FROM Classes WHERE ClassID = Students.Clas

Re: subselect workaround help?

2003-01-08 Thread Rodney Broom
From: Lefevre, Steven <[EMAIL PROTECTED]> > ...I think I need a subselect... It's rare that a sub-select is actually ~needed~, but it does happen. You can almost always get around it with a JOIN of some sort. > Final result should look like > Student | Class > -- > St

RE: subselect workaround help?

2003-01-08 Thread Joe Stump
I think the problem is in your table structure. If you did this: Students: - Name - StudentID Classes: - ClassID - Name StudentsClasses: - StudentID (PK) - ClassID (PK) (You make them a combined key by doing PRIMARY KEY (StudentID,ClassID) in your table def.) When you add a student to a class

RE: subselect workaround help?

2003-01-08 Thread Alexander M. Turek
-Original Message- From: Lefevre, Steven [mailto:[EMAIL PROTECTED]] > > Hey folks- > > 'nother question. > I'm not an SQL expert, and I think I need a subselect, which > means I need a workaround on MySQL 3.23 or whatever version it is. > > Here's the tables I have, with the relevant column

re: Re: Subselect workaround

2002-12-10 Thread Egor Egorov
On Tuesday 10 December 2002 07:48, Amittai Aviram wrote: > Sorry! I meant UPDATE, not INSERT! > > UPDATE administrators SET id = > (SELECT id FROM faculty > WHERE lastName = "Jones") > WHERE ordr = 1; You can use multi-tables updates from 4.0.2 In earlier versions use programming languages.

Re: Subselect workaround

2002-12-09 Thread Amittai Aviram
OOPS! Please note correction below! > Here is what I would want to do: > > INSERT INTO administrators (id) values > (SELECT id FROM faculty > WHERE lastName = "Jones") > WHERE ordr = 1; Sorry! I meant UPDATE, not INSERT! UPDATE administrators SET id = (SELECT id FROM faculty WHERE lastName =

re: subselect solution

2002-10-23 Thread Egor Egorov
Eric, Wednesday, October 23, 2002, 2:06:30 PM, you wrote: EG> I’m a new user on this list as well as Mysql. EG>   EG> I use it with coldfusion EG>   EG> My first problem is the following : EG>   EG>     I have a table where I created a timestamp column named EG> date_creation EG>   EG>   

Re: subselect solution

2002-10-23 Thread Joseph Bueno
Eric Guesdon wrote: > Hi everyone, > > I’m a new user on this list as well as Mysql. > > I use it with coldfusion > > My first problem is the following : > > I have a table where I created a timestamp column named > date_creation > > When I try to execute this sql

re: SubSelect Query

2002-10-03 Thread Egor Egorov
vinita, Thursday, October 03, 2002, 5:56:49 AM, you wrote: vvM> mysql> select userGroup from machineGroup_userGroups where hostname="mulga"; vvM> +---+ vvM> | userGroup | vvM> +---+ vvM> | staff | vvM> | tech | vvM> +---+ vvM> 2 rows in set (0.01 sec) vvM> mysql>

Re: subselect in an insert

2002-07-11 Thread Victoria Reznichenko
daniel, Thursday, July 11, 2002, 3:37:07 AM, you wrote: d> why is it that this doens't work? Because your syntax is wrong. If you want to use INSERT .. SELECT statement, please, look at the manual for correct syntax: http://www.mysql.com/doc/I/N/INSERT_SELECT.html d> INSERT INTO myta

RE: subselect in an insert

2002-07-11 Thread Alain Fontaine
MySQL, as of yet, doesn't support sub-selects ! -Message d'origine- De : daniel [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 11 juillet 2002 02:37 À : [EMAIL PROTECTED] Objet : subselect in an insert why is it that this doens't work? INSERT INTO mytable (column0, column1, column2, column3,

Re: Subselect translation?

2002-06-11 Thread Mark Matthews
Wouldn't "select table1.id from table1, table2 where table1.id != table2.t1id and table2.id = 9" work? Joins don't have to include output columns from both tables, but columns from both tables can (and should, unless you want cartesian products) appear in the WHERE clause. -Mark - Origina

Re: Subselect translation?

2002-06-11 Thread Harrison C. Fisk
I believe the following query will do what you want it to do: SELECT t1.id from table1 as t1 LEFT JOIN table2 as t2 on t2.t1id = t1.id AND t2.id = 9 WHERE t2.id IS NULL; More information can be found at http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html about sub-select work arounds. Hop

Re: Subselect or other way?

2002-05-22 Thread MikeParton
What are you trying to accomplish with the query? What results do you EXPECT to be returned? Can you not do what you're trying to do with: SELECT id FROM Table WHERE (nr=3D123 AND nr=3D456) ? - Original Message - From: "Spielberg Micha" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent:

Re: subselect query

2002-04-15 Thread Christopher Thompson
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Sub-selects On Monday 15 April 2002 2:49 pm, Jeff Shipman wrote: > Yeah, that nasty thing with temporary tables and > such? Or am I looking at the wrong page? If you > could point me in the right direction, I'd

Re: subselect query

2002-04-15 Thread Jeff Shipman
Yeah, that nasty thing with temporary tables and such? Or am I looking at the wrong page? If you could point me in the right direction, I'd appreciate it (slow modem connection, etc). Christopher Thompson wrote: > Read the manual. MySQL doesn't support subselects but offers you > alternatives.

Re: subselect query

2002-04-15 Thread Christopher Thompson
Read the manual. MySQL doesn't support subselects but offers you alternatives. On Monday 15 April 2002 2:45 pm, Jeff Shipman wrote: > I'm trying to do a fairly simple select of a > column from a table where the date of that column > happens to be the highest value. I tried this: > > SELECT name

Re: SubSelect Problem

2002-03-18 Thread Bob Hall
On Tue, Mar 19, 2002 at 12:45:57AM -, Alex Speed wrote: > Ok, no support for it (d'oh), so, how do I go about breaking it down into a > join or something like that? > > - Original Message - > From: "Alex Speed" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, March 19, 2

Re: SubSelect Problem

2002-03-18 Thread Alex Speed
Ok, no support for it (d'oh), so, how do I go about breaking it down into a join or something like that? - Original Message - From: "Alex Speed" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 19, 2002 12:13 AM Subject: SubSelect Problem > SELECT textid, title, descript

RE: SubSelect Problem

2002-03-18 Thread Gurhan Ozen
Hi Alex, MySQL doesn't support sub-selects yet. Gurhan -Original Message- From: Alex Speed [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 7:13 PM To: [EMAIL PROTECTED] Subject: SubSelect Problem SELECT textid, title, description from structure where textid in (select subtopic

RE: SubSelect Problem

2002-03-18 Thread Eric Mayers
Alex, I believe you'll find that MySQL does not support subselects. This is why you're getting the error message. I suggest you consult the SELECT syntax manual page (http://www.mysql.com/doc/S/E/SELECT.html) to see what you can do with selects. You could try to convert this into a join, or

RE: SubSelect Problem

2002-03-18 Thread Dan Vande More
Perhaps I am missing something right in front of me, or I have not been in the loop, but it is my understanding that MySQL does not support sub-selects, yet. http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html BTW, you may receive lots of replies of this very nature, because it is something ev

Re: SubSelect Workaround

2002-01-04 Thread Mr. psm996
> >database,sql,query,table >Can someone please tell me why this won't work. > >if (defined($res)){ > my $stmt = qq{select distinct symbol from portfolio where $res}; > my $sth = $dbh->prepare($stmt); > $sth->execute(); > > my $portsymbols = ""; > > while ($sth->fetchrow) { > $portsymb

Re: SubSelect Workaround help

2002-01-02 Thread Anvar Hussain K.M.
Hi Paul, There is no direct way to make a string by concatinating strings of different columns. But your problem can be solved in a different way. select distinct p.symbol, i.name from portfolio p, portfolio pp, stockinfo i where pp.type = '401k' AND pp.owner = 'jim' and p.symbol = i.symbol a

RE: SubSelect Workaround help

2002-01-02 Thread Haapanen, Tom
Is this too simple? select distinct p.symbol, i.name from portfolio p, stockinfo i where p.symbol = i.symbol and p.type = '401k' and p.owner='jim' order by p.symbol - Tom Haapanen -- Software Metrics/Equitrac Corp. Advanced Printing Solutions -- http://www.metrics.com/ -Original Me