We're testing a bedework system here with an underlying MySQL Database:
Version 5.1.44, Linux x64 (64bit), icc binary from www.mysql.com.
This statement uses 1 minute of 100% CPU:
select summaries0_.bw_eventid as bw1_1_, summaries0_.bw_strid as bw2_1_,
bwstring1_.bw_id as bw1_70_0_,
On Mon, Mar 15, 2010 at 4:22 PM, Pascal Gienger
pascal.gien...@uni-konstanz.de wrote:
Using this result to reconstruct the first left outer join from the initial
statement, I get this result:
Not so much a bug as a missing feature: the parser is currently unable to
recognize any subselect
Waynn Lue wrote:
Out of curiosity, is it generally faster to do a sub query or do it in
code for something like this.
Schema of Settings table, where the PK is (ApplicationId, SettingId):
ApplicationId, SettingId, SettingValue
Select SettingValue from Settings where SettingId = 10 and
, but am
not able to come to a definite conclusion. I read that correlated
subselects are bad, and I should go for the join, but I know the id of
the record in the outer query and can hard code that into the
subselect (if that makes a difference).
The EXPLAIN result only differs
in the
outer query and can hard code that into the subselect (if that makes a
difference).
Sub queries should be avoided if possible in MySQL.
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
PROTECTED] wrote:
I've tried to find out if joins are preferred over subselects, but am not
able to come to a definite conclusion. I read that correlated subselects
are
bad, and I should go for the join, but I know the id of the record in the
outer query and can hard code that into the subselect
I'm having problems optimizing a series of subselects. I have the
following sample table:
mysql select * from fake order by msgid, id desc;
++---+-+-+
| id | msgid | nec | dt |
++---+-+-+
| 10 | 1 | 300 |
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 * from
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
bei 5 Mio
Datensätze in den Abverkäufen PHP schlapp. DArum meine Frage kann ich
effizienter Mit MySQL machen?
Bisher
SELECT ArtikelNr,Kassenbon,Haendler,Datum
FROM sales s
WHERE ArtikelNr = '10099'
SCHLEIFE mit Subselect
SELECT * FROM sales
WHERE ArtikelNr='$ArtikelNr' AND Kassenbon
At 7:45a -0500 on 17 Dec 2007, Marcus Füg wrote:
Tabelle Sales (s) mit jede Menge Abverkaufsdaten.
ArtikelNr,Kassenbon,HändlerID,Datum
Jetzt sollen alle zu einem gekauften Artikel ebenfalls augelistet
werden, d.h. was wurde mit dem Artikel zusätzliche gekauft
Bisher habe ich das mit PHP
` from`forum` where
forumid=`xyz`) AND `post`.`visible`=1 AND`thread`.visible = 1 ORDER BY
`post`.`dateline` DESC LIMIT 5;
The above Query's subselect query doesn't work properly, it lists even the
posts whose forum id is in subselect query.
If the same Query with inner Query replaced by direct
Leelu,
The above Query's subselect query doesn't work properly, it lists
even the posts whose forum id is in subselect query.
Your `post LEFT JOIN thread ON post.threadid=thread.threadid` asks for
post rows whether they have matching thread rows or not. Are you sure
you want that? It sounds
On 4/23/07, Andreas Iwanowski [EMAIL PROTECTED] wrote:
Hello MySQL experts,
I am trying to create a view whose access is based on a User ID that
need to be looked up in a different table.
Here is an example of what I'm trying to do:
CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL
Hello Christian,
Thank you very much for this reply. It was very helpful, and the user
matching part works as it should.
Is there any way to JOIN on two tables, so I can match the Users.GroupID
field against a JOIN on Groups.ID?
Also, I tried adding a WHERE clause after the join to compare
Hello Andreas,
I tried the following statement:
CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL
SECURITY DEFINER VIEW `shared_v` AS SELECT `shared`.`ID` AS
`ID`,`shared`.`RawID` AS `RawID`,`shared`.`OwnerID` AS
`OwnerID`,`shared`.`UserID` AS `UserID`,`shared`.`GroupID` AS
)));
---
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 12:09 PM
To: Andreas Iwanowski
Cc: mysql@lists.mysql.com
Subject: Re: View with Subselect for User ID
Hello Andreas,
I tried the following statement:
CREATE
Hello MySQL experts,
I am trying to create a view whose access is based on a User ID that
need to be looked up in a different table.
Here is an example of what I'm trying to do:
CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL
SECURITY DEFINER VIEW `shared_v` AS select `Shared`.`ID`
),(68);
insert into t2 values (65),(66),(67);
select distinct course_id from t1 where course_id not in (select
course_id from t2);
drop table t1;
drop table t2;
Running on 4.1.13 on SUSE Linux, this doesn't print anything. It
should print those values in t1 but not t2. If I replace the subselect
Linux, this doesn't print anything. It
should print those values in t1 but not t2. If I replace the subselect
with the result of the subselect (65,66,67) then it works as expected.
It also works if I remove the primary key from t2. If I just remove
the primary key from t1 it prints a somewhat mysterious
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
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 with a coworker
about a reasonable application of subselects vs. a WHERE clause or table
join.
Thank you in advance.
--
Chris White
Interfuel
--
MySQL
Hi all,
can someone explain me why this query gets executed the way it is? I
simplified it as much as possible and think it's a conceptual/logical
thing, so I'll omit - at least for now - the table definitions and
sample data for brevity.
-- Superquery
EXPLAIN SELECT t1.id, t1.name
FROM document
On 14/09/2005, Matthias Pigulla wrote:
However - why do we need the dependent subquery at all? The subquery
can be executed on its own, as it does not depend on any information
of the superquery.
This is a subquery optimizer bug.
See http://bugs.mysql.com/bug.php?id=10309
--
felix
--
Hello.
See:
http://bugs.mysql.com/bug.php?id=12106
Matthias Pigulla [EMAIL PROTECTED] wrote:
Hi all,
can someone explain me why this query gets executed the way it is? I
simplified it as much as possible and think it's a conceptual/logical
thing, so I'll omit - at least for
Matthias Pigulla wrote:
Hi all,
can someone explain me why this query gets executed the way it is? I
simplified it as much as possible and think it's a conceptual/logical
thing, so I'll omit - at least for now - the table definitions and
sample data for brevity.
-- Superquery
EXPLAIN SELECT
please help!
i have a fairly large innoDB table with 800mb (index 500 mb, data 300mb) and
1.8 million data sets.
the server has 8 gig ram.
the statement
SELECT id FROM table1 WHERE cityname = 'bla' (cityname has been indexed)
takes 0.0002 seconds and returns 0 rows, which was expected.
you are using and show the full table definition, including
all indices.
Leaving aside that the nesting in the second statement makes no sense at
all (SELECT id WHERE id IN subselect is the same as subselect),
I still fear such a construct will never be fast.
Remember that for any given
Hello.
As mentioned, none of these work and all of these hang the server and
break the database.
Even after weird queries server shouldn't hang and break the database.
Switch to the official binaries of the latest release (4.1.14 now)
and check if the problem remains.
Hi,
I wonder if someone can help me with the following simple(?) MySQL
problem.
I have a table 'action' with about 180,000 web server requests records
running under
MySQL Ver 14.7 Distrib 4.1.12 (Fedora Core 4)
mysqldesc action
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
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
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 it
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
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 |
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,
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 Select
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 same field
Hi,
it is possible to subj?
I have two tables.
create table aaa (id int auto_increment not null, title varchar(255),
primary key (id));
create table bbb (id int auto_increment not null, fk_aaa int not null,
detail varchar(255), primary key (id));
insert into aaa values (1, 'aaa'), (2, 'bbb');
Stano Paska wrote:
Hi,
it is possible to subj?
I have two tables.
create table aaa (id int auto_increment not null, title varchar(255),
primary key (id));
create table bbb (id int auto_increment not null, fk_aaa int not null,
detail varchar(255), primary key (id));
insert into aaa values (1,
Michael Stassen wrote:
Stano Paska wrote:
Hi,
it is possible to subj?
I have two tables.
create table aaa (id int auto_increment not null, title varchar(255),
primary key (id));
create table bbb (id int auto_increment not null, fk_aaa int not
null, detail varchar(255), primary key (id));
insert
. I'm not sure how
I'd do it though and figured I'd see if anyone here knows how.
Here's the subselect that I'd like to use:
select ID from products where CategoryID = (select ID from categories where
ID='21' OR ParentID = '21' )
How would I would I create a query using joins that accomplishes
my production server. The query I
wanted to use would be great, so maybe there's a way to convert it - since
the manual says most subselects can be done with joins. I'm not sure how
I'd do it though and figured I'd see if anyone here knows how.
Here's the subselect that I'd like to use
-
From: Remi Mikalsen [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 5:07 PM
To: [EMAIL PROTECTED]
Subject: Re: Convert subselect query to pre-subselect query
Try this...
select a.ID
from products a, categories b
where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21
Doh... Guess I spoke too soon. I get it now. I wasn't seeing
a.CategoryID=b.ID
=)
-Original Message-
select a.ID
from products a, categories b
where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21')
--
MySQL General Mailing List
For list archives:
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 MySQL 4.1.1a-alpha
[EMAIL
the
average number of requests per time.
I thus thought I can use a subselect like
SELECT AVG(SELECT COUNT(time) anz FROM table GROUP BY time) FROM table
That seems impossible, it in fact simply does not work.
Is it wrong by my design or is it MySQL design not to pass a subselect
to an aggregate
of requests at any time. I now want to see the
average number of requests per time.
I thus thought I can use a subselect like
SELECT AVG(SELECT COUNT(time) anz FROM table GROUP BY time) FROM table
That seems impossible, it in fact simply does not work.
Is it wrong by my design or is it MySQL design
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 on Redhat Linux 8.
--
MySQL General Mailing List
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
Hi,
I have a table SalaryInfo as below
Salary | Department | Level
50 | RD| Director
3 | Maintenance| Groundsman
...
I want to know what level in each department makes the highest salary
and how much that salary is? Something like:
SELECT Salary, Level, Department FROM
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
Hi,
I'm using MySQL 4.0.1 for Linux/Windows... I need to perform a query like this:
SELECT AVALIACAO, COUNT(AVALIACAO)
FROM AUDITORIAS
WHERE IDVERSAO = (SELECT MAX(IDVERSAO) FROM VERSOES WHERE DATA = '2003-08-23')
GROUP BY AVALIACAO
And its doesn't work in this version...
There is other way to
-
From: Oscar (TOMCAT) [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 04, 2003 11:45 PM
Subject: MAX in a SubSelect
Hi,
I'm using MySQL 4.0.1 for Linux/Windows... I need to perform a query like
this:
SELECT AVALIACAO, COUNT(AVALIACAO)
FROM AUDITORIAS
WHERE IDVERSAO
hi there, i am trying to remove values from a list menu if the join table doesnt have
keys when a key is selected for instance:
locations
locationID
locations_join
locationID shotlistID
SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID =
Hello,
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.
I used to set it through MS Access 2002 and use this kind of query:
SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*)
[EMAIL PROTECTED] wrote:
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:
delete from table_a where table_a.column_1 in ( select column_1 from
table_b);
Assuming that column_1 is the same data type
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:
delete from table_a where table_a.column_1 in ( select column_1 from
table_b);
Assuming that column_1 is the same data type and size in both table_a
, I created two tables (Table1 and Table2) and populated them.
The SubSelect executes without error. Obviously something is missing.
Suggestion: Go into MySQL and run the following:
Select maxfecha from fechas;
SELECT * FROM Kardex WHERE Fecha IN (SELECT maxfecha FROM fechas);
And copy
-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 option
it doesnt working?
About the --new startup option for mysqld.exe (win32) you can found it
here: http://www.mysql.com/doc/en/Prepare-upgrade-4.0-4.1.html
Of course in the page say critical changes from the 4.1 version, so I
am just guessing about the support of subselect in 4.0.12.
Any way
[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 1064
| |
+---+-+--+-+-+---+
So im using MySQL 4.1 and still receive the same error for the subselect
query.
And I have other question, i trying to figure how to get the max date
for a certain group of records, here an example:
Cod Product date
10 Nissan Car 2003-06-20
20
date
10Nissan Car 2003-08-10
20Guitar Yamaha 2003-10-05
30Stereo Cables 2003-01-20
That really doesn't need a SubSelect. To simplify things, I created table Q1
with data as follows:
+--+---++
| Cod | Product
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 manual this should enable the
subselect feature
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
-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
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?
(This is on
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?
(This is on mysql version 4.1.0-alpha-max-nt.)
First,
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
Hi MySQL Fans ;-),
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. For xample if I
try this:
CREATE
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. For xample
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. For xample
if I try
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
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
This is a reprise of a question I asked some months ago. Bruce Feist and Tore Bostrup
gave me some untested code to try. I've only now been able to try it, and it's not
quite working right. Here's a recap of the problem I'm trying to solve:
My Web host is running MySQL 3.23, which doesn't
- Original Message -
From: Sheryl Canter [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, June 22, 2003 2:46 PM
Subject: need help with subselect workaround
This is a reprise of a question I asked some months ago. Bruce Feist and
Tore Bostrup gave me some untested code to try. I've only
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
does subselect work in mySQL 3.23 ...i'm trying to
exceute the following query ...
Select can_reg_no from candidate where
(pres_org_curr_desig like '%sales%' or
pres_org_curr_desig like '%marketing%') AND
can_reg_no in
( Select can_reg_no from candidate where
(pres_org_curr_desig like
does subselect work in mySQL 3.23 ...i'm trying to
exceute the following query ...
Select can_ID from TABLE1 where (designation like
'%sales%' or designation like '%marketing%')
AND can_ID in
(Select can_ID from TABLE1 where (designation like
'%telecom%' or designation like '%software
: subselect
does mysql version 3.23 support subqueries ...if no
does latest version 4.0 support...
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net
does mysql version 3.23 support subqueries ...if no
does latest version 4.0 support...
__
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
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 support
Rompaey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: count in subselect problem
Date: Fri, 24 Jan 2003 16:18:04 +0100
MIME-Version: 1.0
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express
1st problem:
SELECT F.id,
F.title,
F.description,
F.created,
(select count(*) from forum F, topic T where F.id=T.forum_id AND
T.reply_id=0 ) count1,
(select count(*) from forum F, topic T where F.id=T.forum_id )
count2
]]
Sent: Thursday, 23 January 2003 5:24 AM
To: [EMAIL PROTECTED]
Subject: solution for opposite of this join / join from this subselect
thanks to brent, bob and M wells for their contributions to this
solution
and to m especially who seems to have put in a lot of time and nailed
it.
This query
See interleaved comments below.
Subject: solution for opposite of this join / join from this subselect
To: [EMAIL PROTECTED]
From: Josh L Bernardini [EMAIL PROTECTED]
Date: Wed, 22 Jan 2003 11:23:44 -0800
thanks to brent, bob and M wells for their contributions to this solution
and to m
thanks to brent, bob and M wells for their contributions to this solution
and to m especially who seems to have put in a lot of time and nailed it.
This query returns a list of people not attending a particular event, given
an events table, a people table, and a many-many epeople table between
Then, you can apply an additional WHERE clause to that.
hth
Subject: join from this subselect
To: [EMAIL PROTECTED]
From: Josh L Bernardini [EMAIL PROTECTED]
Date: Tue, 21 Jan 2003 11:51:13 -0800
I can't come up with the join syntax to mimic this subselect query to list
people not attending
I can't come up with the join syntax to mimic this subselect query to list
people not attending an event (*epeople.eid == event.id):
select people.id as pid, concat(lastname, , , firstname) as name from
people where people.id not in
(select epeople.pid from
epeople
left join people
On Tue, 2003-01-21 at 15:51, Josh L Bernardini wrote:
I can't come up with the join syntax to mimic this subselect query to list
people not attending an event (*epeople.eid == event.id):
select people.id as pid, concat(lastname, , , firstname) as name from
people where people.id
Please help! I already know that my query is wrong.
I have identified it. But what is the solution to my
query?
--- gerald_clark [EMAIL PROTECTED]
wrote: I answered this yesterday.
Your FROM must precede your WHERE.
Terence Ng wrote:
How do I correct this SQL code:
2 tables there,
I answered this yesterday.
Your FROM must precede your WHERE.
Terence Ng wrote:
How do I correct this SQL code:
2 tables there,
lcopen: id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
#this condition :lcreceive.due_date current_date
#only affect to :
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 columns:
Students
- Name
- StudentID (PK)
- ClassID
Classes
- ClassID (PK)
- Name
Each
-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 columns
R.StudentID='364326' AND R.ClassID=C.ClassID
--Joe
--
Joe Stump [EMAIL PROTECTED]
http://www.joestump.net
-Original Message-
From: Lefevre, Steven [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 8:22 AM
To: [EMAIL PROTECTED]
Subject: subselect workaround help?
Hey folks
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
--
Steve
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.ClassID )
How do I correct this SQL code:
2 tables there,
lcopen: id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
#this condition :lcreceive.due_date current_date
#only affect to : SUM(lcreceive.amount_us)
#and NOT :
#SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open
How do I correct this SQL code:
2 tables there,
lcopen: id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
#this condition :lcreceive.due_date current_date
#only affect to : SUM(lcreceive.amount_us)
#and NOT :
#SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open
1 - 100 of 150 matches
Mail list logo