Efficient use of sub queries?

I was wondering if any one could point out potential problems with the following query or if there was a better alternative >From a list of users I want to return all who don't have all the specified user_profile options or those who do not have at least one preference set to 1. The following quer

Re: sub query to daily usage subtraction

-yesterday storage > bases on date .Here id is same. I am new to witting mysql query (learning). > I think this can be done using sub query. any help will be really > appreciated . > > Thanks & Rg > Mohan L >

sub query to daily usage subtraction

erday storage bases on date .Here id is same. I am new to witting mysql query (learning). I think this can be done using sub query. any help will be really appreciated . Thanks & Rg Mohan L

Re: Retrieve three columns in sub query

l Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, August 25, 2010 1:23 PM > To: [MySQL] > Subject: Retrieve three columns in sub query > > Hi > > Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query

Re: Retrieve three columns in sub query

escreveu na mensagem news:aanlkti=ecibcm3kcj7kizprnrkat=nnasfndw5srm...@mail.gmail.com... I thought as much, if anyone else can shed some light that would be great. If not, I'm going to have to write an additional query. 2010/8/25 João Cândido de Souza Neto > As far as I know sub-que

RE: Retrieve three columns in sub query

select ci.*, ct.* from (select name, countrycode from city) ci, country ct where ci.countrycode = ct.code Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 1:23 PM To: [MySQL] Subject: Retrieve three columns in sub

Re: Retrieve three columns in sub query

I thought as much, if anyone else can shed some light that would be great. If not, I'm going to have to write an additional query. 2010/8/25 João Cândido de Souza Neto > As far as I know sub-queries intends to be an only one column and row > result. > > -- > João CÃ

Re: Retrieve three columns in sub query

As far as I know sub-queries intends to be an only one column and row result. -- João Cândido de Souza Neto "Tompkins Neil" escreveu na mensagem news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... > Hi > > Is it possible in MySQL 5.1 to retrieve three

Retrieve three columns in sub query

Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age > 10 ORDER BY RAND() LIMIT 1 I've looked a

RE: Slow when using sub-query

>-Original Message- >From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De >Meersman >Sent: Thursday, June 03, 2010 6:52 AM >To: je...@gii.co.jp >Cc: mysql@lists.mysql.com >Subject: Re: Slow when using sub-query > >The short answer is that

Re: Slow when using sub-query

e the appropriate IN clause, and then run your main query. The long answer is that there's a rather good Advanced Tuning course that addresses all this and more, as does Baron & C° 's excellent MySQL book. On Wed, Jun 2, 2010 at 10:05 PM, Jerry Schwartz wrote: > I've he

Slow when using sub-query

I've heard that sub-queries aren't well-optimized, but this case seems ridiculous. First, a little setup: SELECT pub_id FROM pub WHERE pub_code = 'GD' INTO @P; === Inner Query by Itself === us-gii >SELECT prod_pub_prod_id FROM prod -> WHERE pub_id = @P

Re: how to avoid sub-query to gain performance

On Wed, Jun 2, 2010 at 10:28 AM, Lin Chun wrote: > *don't know it very clear, but i think is the problem of seems it > take full scaning* Yes, it has no indexes so it isn't good for very large subqueries. You should create them as temporary tables instead and give them indexes. - Perrin -- My

how to avoid sub-query to gain performance

*hi* * * *i have a reporting query which have 2 long sub-query* SELECT r1.code_centre, r1.libelle_centre, r1.id_equipe, r1.equipe, r1.id_file_attente, r1.libelle_file_attente,r1.id_date, r1.tranche, r1.id_granularite_de_periode,r1.granularite, r1.ContactsTraites, r1.ContactsenParcage, r1

Re: sub query or something else

Many thanks for your query, seems we need to group it like Wolfgang's does. Willy On Thu, 2009-09-03 at 22:33 -0700, Manasi Save wrote: > may be you can use IN clause: > > SELECT SUM(price)*0.5 AS price1, SUM(price)*0.65 AS price2 FROM table > WHERE partner IN ('A', 'B'); > -- MySQL Gene

Re: sub query or something else

Many thanks for the query. It works ;) Willy On Fri, 2009-09-04 at 08:09 +0200, Wolfgang Schaefer wrote: > sangprabv wrote: > > I have these query: > > SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; > > SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; > > Is it

Re: sub query or something else

sangprabv wrote: > I have these query: > SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; > SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; > Is it possible to make the queries into 1 single query? How to make it > happen? Many thanks for helps. > > > > Willy > > >

Re: sub query or something else

may be you can use IN clause: SELECT SUM(price)*0.5 AS price1, SUM(price)*0.65 AS price2 FROM table WHERE partner IN ('A', 'B'); -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. > I have these query: > SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; > SELECT SUM

Re: sub query or something else

Because these are two quite distinct queries, I don't see an immediate way of joining them that would make them more efficient. Something that comes to mind are sub-select statements for example, but that would make this more complex than it needs to be. Like Robert said, you aren't

Re: sub query or something else

What I'm looking for is to SUM the price from partner A and B with each result. So the result I expect is like "Partner A total's price 123, Partner B total's price 456". Can you give me the query example? TIA. Willy On Thu, 2009-09-03 at 23:11 -0400, Robert Citek wrote: > It's not clear what

Re: sub query or something else

It's not clear what exactly you are looking for. Two possible solutions: 1) use a union 2) use a join with another table containing partner and factor fields. Can you give a short example of what the input looks like and what you would like the output to look like? Regards, - Robert On Thu, S

sub query or something else

I have these query: SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; Is it possible to make the queries into 1 single query? How to make it happen? Many thanks for helps. Willy -- MySQL General Mailing List For li

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisley wrote: > It sounds like you want to use spatial indexes, but they only became > available in v4.1 > http://dev.mysql.com/doc/refman/5.0/en/create-index.html > http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html That "feels" like the rig

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

Peter, On Thu, Jun 18, 2009 at 9:27 PM, Peter Brawley wrote: > For explanation & alternatives see "The unbearable slowness of IN()" at > http://localhost/artful/infotree/queries.php. > you prolly meant to not post a url pointing at your local copy of your website. This works better for most of us

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

'2001-01-01' AND phonedate <= '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-a

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

t includes render time in the > web browser. > > By comparison... the query WHERE id IN (SELECT id FROM phone WHERE > phonedate >= '2001-01-01' AND phonedate <= '2009-06-18') returns > almost instantly. > > I'm at a complete loss... The suggestions

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

>> SELECT zip FROM zipcodes WHERE >> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ >> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 >> < 5 > > Ouch.  You might want to calculate the rectange enclosing your target > distance, add an i

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

In the last episode (Jun 18), Matt Neimeyer said: > I'm converting a PHP app from using Visual FoxPro as the database backend > to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX > 10.4. The end application will be deployed cross platform and to both 4.x > and 5.x MySQL servers

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

te >= '2001-01-01' AND phonedate <= '2009-06-18') returns > almost instantly. > > I'm at a complete loss... The suggestions I've seen online for > optimizing Dependent Subquery's basically revolve around changing it > from a sub-query to a join

Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

ost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since mo

Temporary table vs. sub-select

= `t3`.`y`; 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

Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?

You could create an extra table in order to record the max number of widget,the the ID should alway be 1. On Thu, Oct 16, 2008 at 2:04 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > I would do a muli key PK with a after insert trigger to that would > change widget_number 1 to 1000. Just my HO... >

Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?

I would do a muli key PK with a after insert trigger to that would change widget_number 1 to 1000. Just my HO... > I would use this combo as the primary key, but I hate doing joins with > multiple primary keys, so I'll also keep the widget_id for the purpose of > making joins easier. Why? Both of

How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?

All, I am developing a service in MySQL that models a service I've already built in PostgreSQL. I'm trying to port over some of my ideas from that platform to MySQL. Here's the setup: Let's say I have 2 tables: 'account' and 'widget'. Each of these tables have a primary key but the widget

sub query help

Dear All, I am interested in performing a sub query that removes duplicate records from a temporary table prior to pushing the data to the main table. I am not sure if it is possible and thought I would ask prior to the endeavor. I currently use php to perform this operation but is really bogs

Re: Sub query help

Brian, I think the answer is to create a sub query, Without your tables I can't test this transcription, but the trick is straightforward: if the first query includes the column(s) required to join it correctly to the 2nd query, replace the avgscore table reference in the second query

Re: Sub query help

List'" Sent: Wednesday, May 16, 2007 7:25 PM Subject: Sub query help MySQL Version 5.0.22 I would be thrilled if someone could even just point me to a site that provided good examples of using sub queries. I have the following query: SELECT participants.store

Sub query help

MySQL Version 5.0.22 I would be thrilled if someone could even just point me to a site that provided good examples of using sub queries. I have the following query: SELECT participants.store_id, completed_modules.module_id, AVG(completed_modules.score) AS AVGSCORE FROM

Re: Trouble with using "IN" for a sub-query statement

Chris wrote: Chris White wrote: On Monday 07 August 2006 12:13 pm, William DeMasi wrote: I meant to have it say : "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_

Re: I don¹t have sub queries, can someone suggest alternatives

e from two queries, can someone perhaps show me how to run the same as > above but without the sub query? I am pretty sure I worked this out with the following: DELETE cart2.* FROM cart2 c LEFT JOIN products p ON c.product_id

I don¹t have sub queries, can someone suggest alternatives

s show me how to run the same as above but without the sub query? -- - Scott HanedaTel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- MySQL General Mailing Li

Re: Trouble with using "IN" for a sub-query statement

Chris White wrote: On Monday 07 August 2006 12:13 pm, William DeMasi wrote: I meant to have it say : "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_act_id from c2

Re: Trouble with using "IN" for a sub-query statement

On Monday 07 August 2006 12:13 pm, William DeMasi wrote: > I meant to have it say : > > "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from > > c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_act_id from c2iedm_dev2.act_

Re: Trouble with using "IN" for a sub-query statement

On Monday 07 August 2006 12:02 pm, William DeMasi wrote: > The select statement I am trying to run is: > "select * from c2iedm_dev2.act where act_id =(select obj_act_id from > c2iedm_dev2.act_functl_assoc where subj_act_id =24);" Well the problem I'm seeing is that you're not using IN anywhere in

Trouble with using "IN" for a sub-query statement

The select statement I am trying to run is: "select * from c2iedm_dev2.act where act_id =(select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" But I get the error below: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version f

Re: Sub-query optimizer improvements scheduled?

On Sat, May 06, 2006 at 12:55:55PM +0100, Peter Rosenthal wrote: > Out of interest is there any time on the roadmap to improve the query > optimizer's handling of sub-queries as specified in > http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ? As Timour previewed at

Sub-query optimizer improvements scheduled?

Out of interest is there any time on the roadmap to improve the query optimizer's handling of sub-queries as specified in http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ? Thanks.

Re: optimization - directories and sub-directories (with descendants)

Eli, >Example: I want to search on all the directories under 'd4' that contain the word "music". >I got several solutions, but not satisfying: >A) Loop from 'd4' to sub-dirs in first level, and use buffer list for next iterations >when going deeper

optimization - directories and sub-directories (with descendants)

Hi, I have a table of directories. Each row represents a directory, which holds his name and desc. Another table lists sub-directories from each directory source to its sub-directories targets. dirs: +--+--++ | dir_id | dir_name | dir_desc

RE: Problems getting MySqlDump to use sub-queries

om > Subject: Problems getting MySqlDump to use sub-queries > > I am having a problem using MySqlDump. Context: I am having > to export some "very specific" records from our database > > > > We have a table called BIN which has, amongst others, an ID >

Problems getting MySqlDump to use sub-queries

getting MySqlDump to handle this obviously more complex query statement, it having a sub-query. I have tried lots of variations on the following: mysqldump -u root -p --complete-insert "--where=bin_id in (select id from bin where account_id=19444)" -t otm bin_data &g

Re: need help with user variables in where clause of sub query

Dan Rossi <[EMAIL PROTECTED]> wrote on 12/29/2005 07:19:13 AM: > Thanks for your kind words of opinion, if you feel you have a better > way please do go ahead , i am going to show you the sql i ended up > using which was a union to append the current summary at the end, i > then had to use php

Re: need help with user variables in where clause of sub query

sub queries to return a sum of results, mind you it is not at all possible to do joins for any of this, i was needing to get certain values and caulcations i could not obtain from a sum, group, join of each row. If you think i am an idiot go ahead say so as you already are, im self taught and

Re: need help with user variables in where clause of sub query

ored Procedure instead of making > variables but it didnt even let me do this > > > On 29/12/2005, at 3:31 PM, Dan Rossi wrote: > > > Btwi dont want the column of a view to be a variable, i think thats > > what it thinks ! Im just needing to send the value of the

Re: need help with user variables in where clause of sub query

feedID FROM producers_join WHERE producerID IN (producerID)) something like that, so if functions will work instead of variables for sub queries and views, im still needing to send the primary key of the current row to them somehow. On 29/12/2005, at 3:31 PM, Dan Rossi wrote: Btwi dont want

Re: need help with user variables in where clause of sub query

Btwi dont want the column of a view to be a variable, i think thats what it thinks ! Im just needing to send the value of the current primary key field top a sub query ! Read my latest post if i can get around not using variables, and still manage to get the right values of a current row

Re: need help with user variables in where clause of sub query

d use it for a subquery in that row :| Its not a working query, and im not asking for someone to fix it, however as u can see i need to send the customerID and month to the sub query. What its actually trying to do is tedious to explain, but i have two tables of media usage for a customer, the cur

Re: need help with user variables in where clause of sub query

ld in a row and use it for a > subquery in that row :| Its not a working query, and im not asking for > someone to fix it, however as u can see i need to send the customerID > and month to the sub query. What its actually trying to do is tedious > to explain, but i have two tables of

Re: need help with user variables in where clause of sub query

sub query. What its actually trying to do is tedious to explain, but i have two tables of media usage for a customer, the current month will be in the usage table, so that if the plan changes in that month so does the totals, but for the previous months there is a static month_totals table

Re: need help with user variables in where clause of sub query

Dan Rossi <[EMAIL PROTECTED]> wrote on 12/27/2005 11:39:57 PM: > Hi there i am trying to use usewr variables in a select statement to > add to a where clause in a sub query. Ie > > select @id:=id,@month:=month, (select SUM(totals) from table where > [EMAIL PROTECTED] and

need help with user variables in where clause of sub query

Hi there i am trying to use usewr variables in a select statement to add to a where clause in a sub query. Ie select @id:=id,@month:=month, (select SUM(totals) from table where [EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table its happened on other occasions ie with calculations

Re: Sub Query

n Scheepers wrote: > Hi > > Could anyone help perhaps tell me why the following > simple query containing a sub-query gives a syntax > error. > > select 1 from messages > where not exists ( select 1 from > message_push_notifications >

Re: Sub Query

doesn't like. Rhino - Original Message - From: "Herman Scheepers" <[EMAIL PROTECTED]> To: Sent: Sunday, November 13, 2005 11:53 AM Subject: Sub Query > Hi > > Could anyone help perhaps tell me why the following > simple query containing

Re: Sub Query

Herman Scheepers <[EMAIL PROTECTED]> wrote on 11/13/2005 11:53:23 AM: > Hi > > Could anyone help perhaps tell me why the following > simple query containing a sub-query gives a syntax > error. > > select 1 from messages > where not exists ( select 1 fro

Sub Query

Hi Could anyone help perhaps tell me why the following simple query containing a sub-query gives a syntax error. select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id) Thanx Herman

Re: Sub Selects, Alias Names and stored procedures

L's implementation of SQL :-) Rhino - Original Message - From: "Gordon Bruce" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Friday, November 04, 2005 2:51 PM Subject: Sub Selects, Alias Names and stored procedures After reading one of the recent posts from Gobi [E

Sub Selects, Alias Names and stored procedures

After reading one of the recent posts from Gobi [EMAIL PROTECTED] I took his successful query and modified it for one of my tables. It indeed produce the correct result, but in the process raised some questions. 1. Why do list_ID and acct_ID not have to be qualified with a table name or

Re: Issue with AS and sub queries

---+ Dan Rossi <[EMAIL PROTECTED]> wrote: > Hi there somehow my AS field alias of a sub query is adding a dot at > the start therefore I cant use it in my application. > > (SELECT SUM(feed_usage.bandwidth) AS bandwidth FROM feed_usage WHERE > customerID IN (5

Issue with AS and sub queries

Hi there somehow my AS field alias of a sub query is adding a dot at the start therefore I cant use it in my application. (SELECT SUM(feed_usage.bandwidth) AS bandwidth FROM feed_usage WHERE customerID IN (57) AND DATE_FORMAT(feed_usage.stats_date,'%m%Y')=DATE_FORMAT(NOW(),&#x

Re: Sub Query to long...[solved]

[EMAIL PROTECTED] wrote: My suggestion: Don't use a subquery, use a temp table ( http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html) CREATE TEMPORARY TABLE tmpDupes (KEY (`Barang`)) SELECT `Barang` FROM Barang GROUP BY Barang HAVING count(1) >1; Select b.`BrgId`, b.`Kode`, b.`Barang`

Re: Sub Query to long...

and the result is > the same. So i think mysql not optimized for this kind sub query. > Do you have any other suggestion? > > hendro > > [EMAIL PROTECTED] wrote: > > >hi, > >don't listen to last email. > > > >since the two first rows are

Re: Sub Query to long...

Hi Mathias, Thanks for your suggestion, but i run this query to find multiple records with the same name in field barang (double records). And the results i found 94 rows at 54813 ms. I try your idea and the result is the same. So i think mysql not optimized for this kind sub query. Do you

Re: Sub Query to long...

hi, don't listen to last email. since the two first rows are unique, you can't use my example. Just create an index as i said, and play your query : Select BrgId, Kode, Barang From Barang Where Barang in (Select Barang From Barang Group By Barang Having Count(*) > 1 ) Mathias Selon [EMAIL PRO

Re: Sub Query to long...

I rerezad you and discovered that (BrgId, Kode) is UNIQUE. your query will return no rows :o) spending 54813 ms for nothing. Mathias Selon [EMAIL PROTECTED]: > Hi, > You may have the same table structure in MS, but not the same table definiton > : > constraints+indexes+stats ! > > try : > creat

Re: Sub Query to long...

Hi, You may have the same table structure in MS, but not the same table definiton : constraints+indexes+stats ! try : create index toto on Barang(BrgId, Kode, Barang); Select BrgId, Kode, Barang From Barang Group By Barang Having Count(*) > 1 ; Mathias Selon Hendro Suryawan <[EMAIL PROTECTED

Sub Query to long...

Hi, I have 8414 records in table name Barang, I run query like this : Select BrgId, Kode, Barang From Barang Where Barang in (Select Barang From Barang Group By Barang Having Count(*) > 1 ) and the answer took 54813 ms. I think is too long. I ran the same query against same table in MS SQL Ser

Re: complicated query | no Sub query

e complicated for me to follow. Does it really require more than just 1 or 2 simple select queries to pull out rows ( unique id_secr_rqst - 2 of them for each) which have the max(dt_aud_rec) and second max(dt_aud_rec)? I mean simple queries with joins. I cannot use sub queries. I am assured that

Re: complicated query | no Sub query

Anoop kumar V <[EMAIL PROTECTED]> wrote on 05/24/2005 03:02:11 PM: > Thanks Peter - you gave me some ideas... > here is what I have so far (simplified for simplification..) > > select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2 > where t1.id_secr_rqst=t2.id_secr_rqst > a

Re: complicated query | no Sub query

Thanks Peter - you gave me some ideas... here is what I have so far (simplified for simplification..) select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2 where t1.id_secr_rqst=t2.id_secr_rqst and t1.dt_aud_rec > t2.dt_aud_rec group by t1.id_secr_rqst but the problem is tha

RE: complicated query | no Sub query

Anoop kumar V wrote: > well - actually it might not be the last 2 days - i just want 2 of > the latest records for every task regardless of what date it is in > the table. Okay, now I think I understand what you need - and if I am correct, this looks like one of the m

RE: complicated query | no Sub query

Anoop kumar V wrote: > 1. It returns data about only one id_secr_rqst - I want it to return > data about every id_secr_rqst in the table. So, if I understand you correctly (sorry, having a bad day), you want all records for the past two days? Assuming this, you could u

Re: complicated query | no Sub query

Thanks Peter - but I see two issues: 1. It returns data about only one id_secr_rqst - I want it to return data about every id_secr_rqst in the table. 2. Limit IMO is mysql specific (I hope I am wrong) is there something generic so I dont need to bother about which database I am running it again

RE: complicated query | no Sub query

Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' ORDER by t1.dt_aud_rec DESC LIMIT 2; Peter Normann -- MySQL General Mailing List For list archives:

complicated query | no Sub query

any sub queries - I can do as many joins as necessary. Also the query need not be performance intensive as I dont think we will have more than 2000 rows at any time. Moreover I will have to ignore all rows (or wholes TASKD*'s) where the name_rec_type is closed anywhere. here is what I have

Re: Lost connection to Mysql server during query using a group by clause and sub query

On May 19, 2005, at 12:51 AM, Dan Rossi wrote: Hi there, I am having issues with this funny error message. I am trying to do a sub query and then a group by clause on a date. However i keep getting this annoying message for some reason. Happens via terminal aswell as my sql gui. Here is the

Lost connection to Mysql server during query using a group by clause and sub query

Hi there, I am having issues with this funny error message. I am trying to do a sub query and then a group by clause on a date. However i keep getting this annoying message for some reason. Happens via terminal aswell as my sql gui. Here is the query I am trying to do select (select count

Sub query again

I have worked out, to send that field to the where query, i have to select the outer table again !! i dont think this is very efficient ? select * from complaints, (SELECT IF (count(*) > 1,"Yes","No") AS count FROM complainant ccINNER JOIN complaints c ON cc.complainantID=c.complainantID WHERE c.

Sub Query question

Hi there, I was wondering if its possible to be able to send a field from the outer table to be used as the where statement for the sub query ? something like this select somefield from table 1, (select count(*) from table2 inner join table1 using somekey where table1.key=somefield) as alias

Sub query Help

query to fix the problem, but I can't just change that overall query: remember, this is in a report generator, and arguably a simple one at that. I have to fit everything I need to do into the column sub queries. Is this possible? Is there any way to modify the sub query so that it will i

Re: sub query is extermely slow

artesian product of only 1e8 rows. If it takes a fixed length of time (not a variable length of time) for the query engine to decide if any one row of a JOIN's Cartesian product belongs to the final JOIN results (based on evaluating the ON conditions against each row combination) then the subs

Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date >= '2004-01-01' AND t.date <= '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date >= '2005-01-01'

Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: I would simplify it by converting everything to us explicit (not implicit) JOIN statements,skipping unnecessary type conversions, and logically merging your conditions. Here is your original query, slightly reformatted. SELECT DISTINCT i.basename FROM inventory i, tran

RE: sub query is extermely slow

is probably the largest table? Andy > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 19 January 2005 15:04 > To: sam wun > Cc: mysql@lists.mysql.com > Subject: Re: sub query is extermely slow > > sam wun <[EMAIL PROTECTED]> wrote on

Re: sub query is extermely slow

sam wun <[EMAIL PROTECTED]> wrote on 01/19/2005 07:02:37 AM: > Hi list, > > The following sql statement takes 3 mintues to complete the query. How > can I improve its speed? > select DISTINCT i.basename from inventory i, transaction t, customer c > where i.prodcode = t.prodcode and c.custcode =

RE: sub query is extermely slow

Check ALTER statement in MySQL doc. It explains how to add/modify an index after a table has been created. > -Original Message- > From: sam wun [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 19, 2005 10:00 AM > Cc: mysql@lists.mysql.com > Subject: Re: sub query is

Re: sub query is extermely slow

add index to a field after a table is created? Thanks Sam Clint From: sam wun <[EMAIL PROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the

Re: sub query is extermely slow

ROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table ' (inventory, transact

Re: sub query is extermely slow

LIKE '%buffer%';": Clint From: sam wun <[EMAIL PROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 20:39:41 +0800 Clint Edwards wrote: Sam, Can you send the output of the following:

Re: sub query is extermely slow

-+ 6 rows in set (0.01 sec) Clint From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can

RE: sub query is extermely slow

Sam, Can you send the output of the following: #>explain \G Clint From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can

  1   2   3   4   >