2011/05/21 00:22 +0200, Johan De Meersman
Heh. The parser is pointing out a simple syntax oversight, yes. The correct
syntax for that is select ... from (subselect) aliasname;
And that is required whether the alias is used or not. I at those times have
been tripped up by this requirement when
: Complex Query
=
= 2011/05/21 00:22 +0200, Johan De Meersman
= Heh. The parser is pointing out a simple syntax oversight, yes. The
= correct syntax for that is select ... from (subselect) aliasname;
=
= And that is required whether the alias is used or not. I at those
= times have been tripped up
Subject: RE: Complex Query
To: 'Guido Schlenke' gschle...@hotmail.de, mysql@lists.mysql.com
Date: Friday, 20 May, 2011, 5:07 AM
Hi Guido
I like your powerful query below, but it doesn't answer my question. I guess
some clarification is needed here.
OK, I have books in the database with more than
Schlenke'; mysql@lists.mysql.com; Mimi Cafe
Subject: RE: Complex Query
Hi Guido,
You need to add group by in your query get desire result
SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn,
publisher FROM book INNER
module_nr = ? group by IFNULL(image,'no_image.jpg'),title,
subtitle, pkisbn, publisher;
Mimi
From: Mimi Cafe [mailto:mimic...@googlemail.com]
Sent: 20 May 2011 11:33
To: 'Anupam Karmarkar'
Cc: 'Guido Schlenke'; mysql@lists.mysql.com
Subject: RE: Complex Query
Hi guys
That's cool
Subject: RE: Complex Query
Hi guys
That's cool! It looks like my query was good except that I miss the group
by. Now I only had to remove the as image from the grouping below and it
works fine.
Thanks guys
Mimi
From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
Sent: 20 May
Message -
From: Guido Schlenke galer...@gmx.de
To: mysql@lists.mysql.com
Sent: Friday, 20 May, 2011 9:04:32 PM
Subject: Re: Complex Query
Hi Mimi,
try this
select count(*) from
(SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle,
group_concat(concat(fname,' ',initial
- especially after running a
query with MySQL limit clause.
Mimi
= -Original Message-
= From: Johan De Meersman [mailto:vegiv...@tuxera.be]
= Sent: 20 May 2011 21:16
= To: Guido Schlenke
= Cc: mysql@lists.mysql.com
= Subject: Re: Complex Query
=
= Hmm. Simply replacing the field list with count
I meant it may produce unexpected result.
Mimi
= -Original Message-
= From: Mimi Cafe [mailto:mimic...@googlemail.com]
= Sent: 20 May 2011 22:52
= To: 'Johan De Meersman'; 'Guido Schlenke'
= Cc: mysql@lists.mysql.com
= Subject: RE: Complex Query
=
= Select count(*) from (select
@lists.mysql.com
Sent: Friday, 20 May, 2011 11:52:14 PM
Subject: RE: Complex Query
Select count(*) from (select) did not work. It says: ERROR
1248 (42000): Every derived table must have its own alias and I am
not sure it really mean aliases.
Although select found_rows(); works, I am
Hint:
- group_concat
- group by
- having count(*)1
Cheers
Claudio
2011/5/18 Mimi Cafe mimic...@googlemail.com
Hi
I am trying to retrieve record from 2 tables (book and author), but my
problem is how to retrieve all the names of authors where more than one
author wrote a book.
.
Thanks
Mimi
= -Original Message-
= From: George Pitcher [mailto:george.pitc...@publishingtechnology.com]
= Sent: 19 May 2011 06:42
= To: Mimi Cafe
= Subject: RE: Complex Query
=
= Mimi,
=
= I have a lot of biblio records across several applications. I decided
= at
= the outset
2011/05/18 22:22 +0100, Mimi Cafe
I am trying to retrieve record from 2 tables (book and author), but my
problem is how to retrieve all the names of authors where more than one
author wrote a book.
Here is what i have: it works OK, except that it returns more than one row
for books that have
: George Pitcher [mailto:george.pitc...@publishingtechnology.com]
= Sent: 19 May 2011 06:42
= To: Mimi Cafe
= Subject: RE: Complex Query
=
= Mimi,
=
= I have a lot of biblio records across several applications. I decided
= at
= the outset not to break up the author names. As long as you are able
is created in mysql workbench.
Thanks
Mimi
= -Original Message-
= From: George Pitcher [mailto:george.pitc...@publishingtechnology.com]
= Sent: 19 May 2011 06:42
= To: Mimi Cafe
= Subject: RE: Complex Query
=
= Mimi,
=
= I have a lot of biblio records across several applications. I
Hi Guido
I like your powerful query below, but it doesn't answer my question. I guess
some clarification is needed here.
OK, I have books in the database with more than one author. When I query for
the title of books and name of author, I get more rows for books that have more
than one
Hi
I am trying to retrieve record from 2 tables (book and author), but my
problem is how to retrieve all the names of authors where more than one
author wrote a book.
Here is what i have: it works OK, except that it returns more than one row
for books that have more than one author.
select
I have three tables from which I need to query for a keyword. The 3 tables
are products, productsubcategories and productcategories. Fron these three
tables, I need to query for a product name or keywork starting with the
products table. if the keyword is not found in the product name or
Hello,
I don't know how indexes work is something I hear often. I will try
to answer in the form of a blog post when I have some time. It is too
complex to really answer in a short form, but basically an index is a
b-tree (look at wikipedia for a good explanation) built on top of the
.
-Original Message-
From: Hugo Ferreira da Silva [ mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] ]
Sent: 14 August 2007 19:19
To: mysql@lists.mysql.com
Subject: Re: Problem with a complex query
I've created an index with the statement
create index `usuario_pasta_situacao` on `mensagens
I found something weird.
This is my query now
--
(SELECT
m.codmensagem,
m.codprioridade,
m.codusuario,
m.codmensagemoriginal,
m.codmensagempai,
m.assunto,
m.dataenvio,
m.horaenvio,
m.datalimite,
m.horalimite,
m.anexo,
m.tipo,
u.nome,
up.nome as nomepara,
mp.codrespondida,
To: mysql@lists.mysql.com
Subject: Re: Problem with a complex query
I found something weird.
This is my query now
--
(SELECT
m.codmensagem,
m.codprioridade,
m.codusuario,
m.codmensagemoriginal,
m.codmensagempai,
m.assunto,
m.dataenvio,
m.horaenvio,
m.datalimite,
m.horalimite
[mailto:[EMAIL PROTECTED]
Sent: 15 August 2007 13:35
To: mysql@lists.mysql.com
Subject: Re: Problem with a complex query
I found something weird.
This is my query now
--
(SELECT
m.codmensagem,
m.codprioridade,
m.codusuario,
m.codmensagemoriginal,
m.codmensagempai
Hi,
I've done some indexes in my tables and I solved my problem.
But I still confused with indexes. I created one multiple index with the
main 4 columns for mensagenspara's table. But it doesn't work. So, I created
one index with 3 columns and one for each column, wich give me 4 indexes,
and
Hi,
I have a message system wich work in this way:
- Each message can be sent to one or more recipients
- Each message received have a lot of settings, like date and hour received,
date and hour of read, mark as read and so on
- When the user select view message, the system will get the history
PROTECTED]
Sent: 14 August 2007 16:04
To: mysql@lists.mysql.com
Subject: Problem with a complex query
Hi,
I have a message system wich work in this way:
- Each message can be sent to one or more recipients
- Each message received have a lot of settings, like date and hour received,
date and hour
, August 14, 2007 11:04 AM
Subject: Problem with a complex query
Hi,
I have a message system wich work in this way:
- Each message can be sent to one or more recipients
- Each message received have a lot of settings, like date and hour
received,
date and hour of read, mark as read and so
hum... I get it.
But in my query, I look in 3 tables
FROM
mensagens m,
mensagenspara mp,
usuarios u,
usuarios up
WHERE
m.codmensagem = mp.codmensagem
AND u.codusaurio = m.codusuario
AND up.codusuario = mp.codusuario
m.codmensagem, u.codusaurio and up.codusuario are primary keys
mp.codusuario,
what does explain tell you about the query?
I also think you should try a combined index with all three columns
that are used in one index. As far as I know only one index can be
used per query...
B
On Aug 14, 2007, at 1:26 PM, Hugo Ferreira da Silva wrote:
hum... I get it.
But in my
The indexes are the primary way of tuning your query speed but bear in
mind that mysql can only use 1 index-per-table-per-query.. your
single column indexes do help some.
try an EXPLAIN to see what I mean:
EXPLAIN SELECT ...
This will show you how mysql is approaching the query. From your
I've created an index with the statement
create index `usuario_pasta_situacao` on `mensagens` (codusuario, codpasta,
situacao);
And for mensagenspara table this index
create index `mensagem_usuario_pasta_situacao_idx` on `mensagenspara`
(codmensagem, codusuario, codpasta, situacao);
This is
From explain you can see that your problem lies within the mensagens
table (the first entry from your explain query) where it says type:
ALL and rows 68337. This basically means that it is not using any
index for this table.
MySQL doesn't seem to be very smart about queries involving OR
MySQL doesn't seem to be very smart about queries involving OR and
things like . For me creating temporary tables or writing perl
scripts to do the job solved my particular problems. But I am working
with tables that don't change but have some 100,000,000 rows...
I guess I suggest,
I have a database tracking registrations of software products with two related tables, registered_products and people. I need to pull a list of email addresses from the people table BUT I only want to pull people records who own very specific combinations of products from the registered_products
[EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, August 2, 2007 1:22:00 PM (GMT-0500) America/New_York
Subject: Complex query
I have a database tracking registrations of software products with two related
tables, registered_products and people. I need to pull a list of email
addresses from
Hi Benjamin,
Benjamin Ventura wrote:
I have a database tracking registrations of software products with two
related tables, registered_products and people. I need to pull a
list of email addresses from the people table BUT I only want to pull
people records who own very specific combinations
and B.product_type = Product B
;
Give it a try !!!
- Original Message -
From: Benjamin Ventura [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, August 2, 2007 1:22:00 PM (GMT-0500) America/New_York
Subject: Complex query
I have a database tracking registrations of software products
I have a nice little query that I can get the results from fine, but am
having problems figuring out the best way to get the count directly from
mysql. The query looks like the following:
SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2, t2.col3, t2.col4 FROM t1
JOIN t2 ON ( t2.id = t1.col1 )
-Original Message-
From: Les Fletcher [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 01, 2007 3:27 PM
To: mysql@lists.mysql.com
Subject: counting on a complex query
I have a nice little query that I can get the results from
fine, but am
having problems figuring out the best way to get
On 8/1/07, Jerry Schwartz [EMAIL PROTECTED] wrote:
Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want?
Be careful. While it will give the correct number of rows that would
have been found if LIMIT had not been used, it makes the server
compute the whole result set,
SQL_CALC_FOUND_ROWS isn't an option for us. Right now I am doing two
queries, but I am just trying to see if there is a better way to do the
count query than to just turn it into a dervied table and count the
results i.e. SELECT COUNT(*) FROM (big nasty query) t1.
I think what the question
On 8/1/07, Les Fletcher [EMAIL PROTECTED] wrote:
SQL_CALC_FOUND_ROWS isn't an option for us. Right now I am doing two
queries, but I am just trying to see if there is a better way to do the
count query than to just turn it into a dervied table and count the
results i.e. SELECT COUNT(*) FROM
important) and delete all BUT ONE! I
have no clue as to how i can delete all but one... Also the rest of the data
may differ a little bit (datetimes for example) so i can't group them
Any ideas?
--
View this message in context:
http://www.nabble.com/Mysql-Complex-Query-to-clear-Duplicate-entries
(datetimes for example) so i can't group them
Any ideas?
--
View this message in context:
http://www.nabble.com/Mysql-Complex-Query-to-clear-Duplicate-entries...-tf2331150.html#a6485034
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives
I will explain my problem in spanish and english.
Se deben Presentar los consumos por 24 horas de energia para su ingreso. Si
el usuario ha digitado 10 registros, se deben presentar estos mas los otros
14 correspondientes desde la hora 11 hasta la 24 con los consumos en cero.
Siempre un conjunto
ElkinFernando Ortiz [EMAIL PROTECTED] wrote on 03/10/2006 12:26:57
AM:
I will explain my problem in spanish and english.
Se deben Presentar los consumos por 24 horas de energia para su ingreso.
Si
el usuario ha digitado 10 registros, se deben presentar estos mas los
otros
14
ElkinFernando Ortiz wrote:
How i calculate for union the other 14
register in the same Query?
...
SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where
e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.H
By having an hours table (hour tinyint) which you join to your energy
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
Shipped Undelivered Returned Open
12/8/2005 143 3 3
Does this mean of the 14 shipped on 12/8/2005, 3 were returned at some
later date, or does it mean that you shipped 14 on 12/8/2005, and on
that same day 3 unrelated shipments came back, each of which
ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0
Sorry - I think you need a LEFT JOIN or it won't count shipments which are
not returned.
Alec
[EMAIL PROTECTED]
12/08/2005 16:38
To
[EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I
-Original Message-
From: James M. Gonzalez
Sent: 12 August 2005 16:58
To: 'Scott Noyes'
Subject: RE: Complex query. (It's killing me)
Sorry, I will explain myself more clearly:
Everyday, we ship packages, and we also receive some packages.
The one we receive, has been shipped by us
of conditions (like where clausules)
James
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 12 August 2005 16:41
To: [EMAIL PROTECTED]
Cc: James M. Gonzalez; mysql@lists.mysql.com
Subject: Re: Complex query. (It's killing me)
Sorry - I think you need a LEFT
mysql Ver 12.22 Distrib 4.0.20, for mandrake-linux-gnu (i586)
With 4.1, it might have been a little easier using some subqueries.
But with 4.0, I don't think we can get the results you're looking for
in a single query, without some really nasty setup. Part of the issue
is that we need to join
Hi,
I'm trying hard to figure out how to perform a special query in mysql 4.0.
I have one table widgets which has a column widget-id (int) and one
column number_of_parts (int).
And then I have another table part_mapping which has one column
widget-id (int) and one column part_id (int).
part_id
Philip Lawatsch [EMAIL PROTECTED] wrote on 06/06/2005 01:37:37 PM:
Hi,
I'm trying hard to figure out how to perform a special query in mysql
4.0.
I have one table widgets which has a column widget-id (int) and one
column number_of_parts (int).
And then I have another table part_mapping
Philip Lawatsch wrote:
Hi,
I'm trying hard to figure out how to perform a special query in mysql 4.0.
I have one table widgets which has a column widget-id (int) and one
column number_of_parts (int).
And then I have another table part_mapping which has one column
widget-id (int) and one
Unless you are keeping track of whether a widget in complete or not,
there is no hope but to scan the entire table to determine if a widget
is complete. That's something you don't want to do. You should mark a
widget as complete when it is completed. This would mean checking if a
particular
[EMAIL PROTECTED] wrote:
I pretty much have no idea how I can do this without nested queries (and
to be frank not even how to do it with them) so I'd really appreciate
any help!
kind regards Philip
Try this as a starting point:
snip
I think you were having two mental problems: 1) how
Hello,
I have a search query to build on two table below is the table structure, and I
need to build a query to search on specific cities(or all cities),specific
subjects(or all subjects) and specific levels(or all levels)
# Table structure for tt_jobs and tt_jobbids
# tt_jobs
pid int(6)
Hi,
I am trying to come up with the most optimal way to write a query to select out
the following fields:
test_username.username_name
test_subprofile_details.subprofile_details_headline
test_profile.profile_birthday
test_profile_gender.profile_gender_name
test_subprofile.subprofile_picture
Using MySQL: 4.23.46
The select query below returns a grouped list of all featured artists. Their images
are listed in a random order within each artist group.
Is it possible to still return *all* featured artists (there are 6) but only a random
selection of no greater than 2 images from
Using MySQL: 3.23.46 (Correction)
The select query below returns a grouped list of all featured artists. Their images
are listed in a random order within each artist group.
Is it possible to still return *all* featured artists (there are 6) but only a random
selection of no greater than 2
Hello friends,
I have a problem with one of the JOIN query here.
Below is a brief description of the problem.
tablename : test
RecordId EffectiveDate othertableidvalue
- -
1 2004-01-10 1 10
2
hi vijay,
You can use order by group by commands in the queries..
Thanks and Regs,
- Aman.
On Mon, 19 Jan 2004, Vijay Patel wrote:
Hello friends,
I have a problem with one of the JOIN query here.
Below is a brief description of the problem.
tablename : test
RecordId
Bob Terrell wrote:
on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote:
Thanks for the info. Just so I'm sure I know what I should: :)
Thanks for your answer, very helpful!
?
$suma = 0;
For starters, you won't need this line anymore. You'll see why in a minute.
Bob Terrell wrote:
So there you go. Give it a shot and see if it runs any faster. If you need the time,
replace that first test in the WHERE clause with the one mentioned above. If this is
run often (or if it just takes a _really_ long time), put an index on the date
field, if one isn't
Bob Terrell wrote:
on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote:
Tell me if you need any additional information.
Thank you for all your help.
Some additional information would help, yes. What exactly are the sections, for
example? Do you need to know the number of new
on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote:
Thanks for the info. Just so I'm sure I know what I should: :)
1) You only want the day's articles. These are records in the article table and
nowhere else.
2) An article will always belong to at least one section, and may belong to
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the new
documents in the repository, where new is
defined as from 00:00:01 up to 23:59:59
today. My current query does not give me
satisfactory results, it creates a
One other thing to check is that there are indexes on the fields in the other tables
that you are joining to.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote:
Tell me if you need any additional information.
Thank you for all your help.
Some additional information would help, yes. What exactly are the sections, for
example? Do you need to know the number of new _articles_ total, the new
In all probability it's (x_section.Status 1) = 0 and
(x_instance.Status 255) = 0 that's giving you the problem.
Unfortunately this is a database schema problem not a query fix. By putting
a computation on a field into the WHERE clause, you're forcing the database
to do that computation on
* Elisenda
[...]
So, the good way to write joins will be as follows, doesn't it?
FASE.SQL_ID_CE=CA.CA_ID_CE AND
FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND
CA.CA_ID_CE=CE.CE_ID_CE AND
FASE.AU_PR_AULA=AU.AU_AULA
Those are the exact same criteria as you had, only in a different order,
isn't it? The
Roger,
* Elisenda
[...]
The explain select says as follows:
[...]
I re-formatted the query and the EXPLAIN output for readability:
Sorry for not re-formatted the query, I've learn it for next time.
Well, I've learn a lot with your lessons.
Select FASE.PR_Date_Visita_2, CE.CE_Centro,
Roger,
Your help was fantastic. It seems that it goes better. At the end I achieve
some result but not all I need.
The explain select says as follows:
EXPLAIN Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio,
CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV,
* Elisenda
[...]
The explain select says as follows:
[...]
I re-formatted the query and the EXPLAIN output for readability:
Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP,
CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA,
CA.CA_Horario,
PP.PP_Contacto,
I'm sorry I didn't explain anything.
The problem is that it 's too slow and the result doesn't appear. I am going
to try to explain the query.
Fields from Table FASE: (300.000 records)
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
SQL_ID_PY char(6),
SQL_ID_CE char(6),
PR_flag INT,
* Elisenda
The problem is that it 's too slow and the result doesn't appear.
I am going to try to explain the query.
Please do that using the EXPLAIN SELECT statement:
URL: http://www.mysql.com/doc/en/EXPLAIN.html
This will show what index is beeing used on the different joins, and
I have a query which tries to select different fields from 5 different
tables.
In WHERE part I have write all the conditions and relationships. Perhaps two
many.
The main table for me is FASE. From this table I try to find all the other
information.
I guess I'm doing something wrong but I
* Elisenda
I have a query which tries to select different fields from 5 different
tables.
In WHERE part I have write all the conditions and relationships.
Perhaps two many.
Joining 5 tables should not be a problem, but having indexes on the relevant
columns may be essential, especially
it would help alot if you dump the table structure for us
- Original Message -
From: Elisenda
To: [EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 7:10 PM
Subject: Some help with a complex query
I have a query which tries to select different fields from 5 different
], [EMAIL PROTECTED]
Subject: Re: Complex query woes
have you try left join?
select
mgr.company,
building.bldgname,
tenant.id
from
customers mgr
left join customers building on building.pid=mgr.id
left join costumers tenant on tenant.pid=building.id
group by mgr.id, building.id, tenant.id
index my_id (id);
-leo-
- Original Message -
From: Steffan A. Cline
To: Leo ; [EMAIL PROTECTED]
Sent: Monday, November 10, 2003 8:12 PM
Subject: Re: Complex query woes
Leo,
Thanks for the quick reply. There was a typo but I fixed it. Below is
what I used after
I am hoping someone here might have an idea how to set up a complex query I
have been trying to figure out.
Basically I have a table that contains 3 types of records. Property
managers, buildings and tenants. They are related upon insert by an ID and a
PID (parent id). For example :
ID PID
hopefully it work :)
-leo-
- Original Message -
From: Steffan A. Cline
To: [EMAIL PROTECTED]
Sent: Monday, November 10, 2003 12:56 PM
Subject: Complex query woes
Basically I have a table that contains 3 types of records. Property
managers, buildings and tenants
Hi,
I'm looking at some search engine where we can have up to 14-15 categories and each
categories have about 5-10 option (checkbox)
my question is if we have a complex query like this
would it be better to split some categories in tables
i.e we have 21 different categories should we have 7
Believe that a respondent sent an email which appears to have been
bounced deleted before I got a chance to view it. If you are the
person, please resend your response! Thank you.
===
Very complex Grouping and counting challenge
Can anyone offer
Very complex Grouping and counting challenge
Can anyone offer guidance and suggest SQL which will assist in
resolving this complex and challenging (for me) issue? I hope that
my attempt to clearly state this problem is successful.
The Environment:
Queries and presentation using PHP Version
a complex query, We have been surprised to
ml find that using EXPLAIN could hang it as well, even with freshly rebuilt
ml and analyzed indexes. If there is no data, EXPLAIN fails saying Impossible
ml WHERE notices after reading const tables, as there is nothing there. I can
ml provide sample data
joins and nested where clauses.
Description:
The query analyzer seems to hang while trying to parse/optimize this
particular query if there is data in the table. Trying to understand why
mysql locked up running a complex query, We have been surprised to
find that using EXPLAIN could hang
corresponding number of invitations and a breaddown of inviations to show
total number of each invitation_status
- Original Message -
From: Sammy Lau [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, November 30, 2002 6:01 AM
Subject: Re: Complex Query
try this:
select a.name
Hi,
I need help with a complex query that I can't figure it out myself.
Suppose that I have the following tables
1) stations
-id
-name
2) invitations
-id
-station_id
-invitation_status (confirmed, cancelled, postponed)
I need a query that generate
try this:
select a.name, b.invitation_status, count(*) from stations a, invitations b
where a.id = b.station_id group by a.name, b.invitation_status
- Original Message -
From: Ahmed Farouk [EMAIL PROTECTED]
Date: Fri, 29 Nov 2002 22:50:34 +0200
To: [EMAIL PROTECTED]
Subject: Complex Query
Hello,
I am relatively new to MySQL though I have database experience.
I have a query that doesn't seem to want to use an index for the first
table despite my indexing several fields. Before I get too far in the
details of the query, here is what EXPLAIN SELECT tells me for the
first
Try - and I mean *try* :-)
SELECT Customers.* FROM Customers, Subscriptions
LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID)
LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id
(Ordered_Items.productid = '23'))
WHERE Customers.id=Subscriptions.customerid
AND
coffee or some sleep
Peter Normann
-Original Message-
From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]]
Sent: 18. juni 2002 22:21
To: Peter Normann; [EMAIL PROTECTED]
Subject: Re: Need help with a complex query
Try - and I mean *try* :-)
SELECT Customers.* FROM Customers
I am currently a web designer who's taught himself the necessary basics of
PHP and MySQL. I've reached a point where I can't figure out a complex
query.
I have a mailing list that customers can sign up for if they want to receive
Special Offer mailings when products go on sale. So when it's time
AND Subscriptions.listid='2'
AND Ordered_Items.orderid IS NULL
AND Orders.customerid IS NULL
AND NOT Customers.bad_email;
Peter Normann
-Original Message-
From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]]
Sent: 16. juni 2002 18:23
To: [EMAIL PROTECTED]
Subject: Need help with a complex query
I am currently
Try - and I mean *try* :-)
SELECT Customers.* FROM Customers, Subscriptions
LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID)
LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id
(Ordered_Items.productid = '23'))
WHERE Customers.id=Subscriptions.customerid
AND
1 - 100 of 110 matches
Mail list logo
