Very slow subselect (parser bug)?

[Pascal Gienger]

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_, bwstring1_.bwseq as bwseq70_0_, 
bwstring1_.bw_lang as bw3_70_0_, bwstring1_.bw_value as bw4_70_0_ from 
bw_event_summaries summaries0_ left outer join bw_strings bwstring1_ on 
summaries0_.bw_strid=bwstring1_.bw_id where summaries0_.bw_eventid in 
(select bweventobj1_.eventid from bw_recurrences bwrecurren0_ inner join 
bw_events bweventobj1_ on bwrecurren0_.masterid=bweventobj1_.eventid 
where (bwrecurren0_.bw_rstart_floating='T' and 
bwrecurren0_.start_date'20100322T00Z' and 
(bwrecurren0_.end_date'20100315T00Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100315T00Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date'20100321T23Z' and 
(bwrecurren0_.end_date'20100314T23Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100314T23Z')) and 
bweventobj1_.bw_col_path='/user/pascal.gienger/Training' );


Result:

+++---++---+--+
| bw1_1_ | bw2_1_ | bw1_70_0_ | bwseq70_0_ | bw3_70_0_ | bw4_70_0_ 
  |

+++---++---+--+
|   2251 |   2674 |  2674 |  0 | NULL  | MLBf 
  |
|   2493 |   2933 |  2933 |  0 | NULL  | Cvlqxpqz 
  |
|   2848 |   3322 |  3322 |  0 | NULL  | Vlqehfhva 
  |
|   2927 |   3405 |  3405 |  0 | NULL  | fb.nt-Lkqivqm 
  |
|   3057 |   3557 |  3557 |  0 | NULL  | Glqsd 
  |
|   3116 |   3616 |  3616 |  0 | NULL  | KGWG 
  |
|   3212 |   3718 |  3718 |  0 | NULL  | MlwfQPE 
  |
|   3361 |   3873 |  3873 |  0 | NULL  | Eklmzmmlsfeei 
  |
|   3573 |   4098 |  4098 |  0 | NULL  | 
HYX-Rjdmmtyubvbqeczvwxz  |
|   3622 |   4149 |  4149 |  0 | NULL  | Uayfhjjlge 
  |
|   3741 |   4283 |  4283 |  0 | NULL  | Sflnh 
QK/Flkudduiwmkbdqz |

+++---++---+--+
11 rows in set (1 min 1.69 sec)


Executing only the second select, I'll get this result:


select bweventobj1_.eventid from bw_recurrences bwrecurren0_ inner join 
bw_events bweventobj1_ on bwrecurren0_.masterid=bweventobj1_.eventid 
where (bwrecurren0_.bw_rstart_floating='T' and 
bwrecurren0_.start_date'20100321T00Z' and 
(bwrecurren0_.end_date'20100314T00Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100314T00Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date'20100320T23Z' and 
(bwrecurren0_.end_date'20100313T23Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100313T23Z'));


+-+
| eventid |
+-+
|3622 |
|3573 |
|3212 |
|3116 |
|2251 |
|2927 |
|2493 |
|3057 |
|2848 |
|3212 |
|3361 |
|2493 |
|3057 |
|3741 |
|3212 |
+-+

Using this result to reconstruct the first left outer join from the 
initial statement, I get this result:


select summaries0_.bw_eventid as bw1_1_, summaries0_.bw_strid as bw2_1_, 
bwstring1_.bw_id as bw1_70_0_, bwstring1_.bwseq as bwseq70_0_, 
bwstring1_.bw_lang as bw3_70_0_, bwstring1_.bw_value as bw4_70_0_ from 
bw_event_summaries summaries0_ left outer join bw_strings bwstring1_ on 
summaries0_.bw_strid=bwstring1_.bw_id WHERE summaries0_.bw_eventid IN 
(3622,3573,3212,3116,2251,2927,2493,3057,2848,3212,3361,2493,3057,3741,3212);


+++---++---+--+
| bw1_1_ | bw2_1_ | bw1_70_0_ | bwseq70_0_ | bw3_70_0_ | bw4_70_0_ 
  |

+++---++---+--+
|   2251 |   2674 |  2674 |  0 | NULL  | MLBf 
  |
|   2493 |   2933 |  2933 |  0 | NULL  | Cvlqxpqz 
  |
|   2848 |   3322 |  3322 |  0 | NULL  | Vlqehfhva 
  |
|   2927 |   3405 |  3405 |  0 | NULL  | fb.nt-Lkqivqm 
  |
|   3057 |   3557 |  3557 |  0 | NULL  | Glqsd 
  |
|   3116 |   3616 |  3616 |  0 | NULL  | KGWG 
  |
|   3212 |   3718 |  3718 |  0 | NULL  | MlwfQPE 
  |
|   3361 |   3873 |  3873 |  0 | NULL  | Eklmzmmlsfeei 
  |
|   3573 |   4098 |  4098 |  0 | NULL  | 
HYX-Rjdmmtyubvbqeczvwxz  |
|   3622 |   4149 |  4149 |  0 | NULL  | Uayfhjjlge 
  |
|   3741 |   4283 |  4283 

Re: Very slow subselect (parser bug)?

[Johan De Meersman]

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 as being fully independent, and will thus execute it
for each and every row in your primary select.

You may or may not get better results by rewriting it so the subselect is a
virtual table. If that doesn't help, subselect into temptable and use that,
or do the reconstruction in code, or other dirty tricks. YMMV.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Picking the better query (join vs subselect)

[walter harms]

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
 ApplicationId IN (select ApplicationId from Settings where SettingId =
 22 and SettingValue = 1);
 
 The other solution is to do the two queries separately then do the
 filtering in code.
 
 What's generally faster?
 
 Waynn
 

there is no easy answer.
subqueries with constants are fast in mysql without is is better to use a join.
complex queries involving several tables with perhaps a lot foreign keys tend 
to be
very slow. using join from coreutils can improve things dramaticly.

re,
 wh

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Picking the better query (join vs subselect)

[Morten Primdahl]

Hi guys,

I have 2 tables cars and parts where car has many parts. I need a  
query to return some fields from the cars table as well as a field  
from multiple parts records. I've come to the following approaches,  
and would like to understand which is the better, and why, or if  
there's a 3rd and even better approach:


Approach 1 - subselects:

SELECT cars.id,
   (SELECT parts.value FROM parts WHERE car_id = cars.id AND id =  
3) AS part_3,
   (SELECT parts.value FROM parts WHERE car_id = cars.id AND id =  
4) AS part_4

FROM cars
WHERE id = 2;

Approach 2 - joins:

SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND   parts_4.id = 4
AND   cars.id = 2;

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 (if that makes a difference).


The EXPLAIN result only differs in that the select_type is SIMPLE in  
the JOIN approach, but PRIMARY/DEPENDENT SUBQUERY in the subselect  
approach.


Any tips much appreciated, the full example below.

Br,

Morten


CREATE TABLE cars (
  id integer,
  make varchar(32)
);

CREATE TABLE parts (
  id integer,
  car_id integer,
  value varchar(64)
);

INSERT INTO cars (id, make) VALUES (1, 'Ford');
INSERT INTO cars (id, make) VALUES (2, 'Honda');

INSERT INTO parts (id, car_id, value) VALUES (1, 1, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (2, 1, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (3, 2, 'Wheel');
INSERT INTO parts (id, car_id, value) VALUES (4, 2, 'Tire');
INSERT INTO parts (id, car_id, value) VALUES (5, 2, 'Dice');

SELECT cars.id,
   (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 3) AS  
part_3,
   (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 4) AS  
part_4

FROM cars
WHERE id = 2;

SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4
FROM cars
INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id
INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id
WHERE parts_3.id = 3
AND   parts_4.id = 4
AND   cars.id = 2;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Picking the better query (join vs subselect)

[Rob Wultsch]

On Wed, Aug 6, 2008 at 5:18 AM, Morten Primdahl [EMAIL 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 (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://lists.mysql.com/[EMAIL PROTECTED]

Re: Picking the better query (join vs subselect)

[Waynn Lue]

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
ApplicationId IN (select ApplicationId from Settings where SettingId =
22 and SettingValue = 1);

The other solution is to do the two queries separately then do the
filtering in code.

What's generally faster?

Waynn



On 8/6/08, Rob Wultsch [EMAIL PROTECTED] wrote:
 On Wed, Aug 6, 2008 at 5:18 AM, Morten Primdahl [EMAIL 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 (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://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

subselect logic

[Kip Turk]

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 | 2008-06-25 09:18:05 |
|  9 | 1 | 301 | 2008-06-25 09:18:02 |
|  6 | 1 | 305 | 2008-06-25 09:15:40 |
|  5 | 1 | 301 | 2008-06-25 09:15:32 |
|  2 | 1 | 301 | 2008-06-25 09:15:10 |
|  1 | 1 | 300 | 2008-06-25 09:15:04 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  8 | 2 | 305 | 2008-06-25 09:17:49 |
|  4 | 2 | 305 | 2008-06-25 09:15:19 |
|  3 | 2 | 301 | 2008-06-25 09:15:14 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 13 | 5 | 301 | 2008-06-25 09:23:33 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

I'm trying to grab and count the nec for the highest id entry for each 
distinct msgid.  To get the correct entries, I can use:


mysql select * from (select * from fake order by id desc) as fake1 
group by msgid;

++---+-+-+
| id | msgid | nec | dt  |
++---+-+-+
| 10 | 1 | 300 | 2008-06-25 09:18:05 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

And to get the counts, I can use:
mysql select nec, count(nec) as count from (select * from (select * 
from fake order by id desc) as fake1 group by msgid) as fake2 group by nec;

+-+---+
| nec | count |
+-+---+
| 300 | 4 |
| 305 | 2 |
+-+---+

So on my tiny test table, the logic is valid to get the results I want. 
 However, on my actual table with several million lines, the nested 
selects makes this a pretty ugly option (to the point even explain took 
a few minutes to respond).  What can I do to optimize this  query?


Thanks,
Kip Turk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: subselect logic

[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 * from fake order by msgid, id desc;
++---+-+-+
| id | msgid | nec | dt  |
++---+-+-+
| 10 | 1 | 300 | 2008-06-25 09:18:05 |
|  9 | 1 | 301 | 2008-06-25 09:18:02 |
|  6 | 1 | 305 | 2008-06-25 09:15:40 |
|  5 | 1 | 301 | 2008-06-25 09:15:32 |
|  2 | 1 | 301 | 2008-06-25 09:15:10 |
|  1 | 1 | 300 | 2008-06-25 09:15:04 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  8 | 2 | 305 | 2008-06-25 09:17:49 |
|  4 | 2 | 305 | 2008-06-25 09:15:19 |
|  3 | 2 | 301 | 2008-06-25 09:15:14 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 13 | 5 | 301 | 2008-06-25 09:23:33 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

I'm trying to grab and count the nec for the highest id entry for each 
distinct msgid.  To get the correct entries, I can use:


mysql select * from (select * from fake order by id desc) as fake1 
group by msgid;

++---+-+-+
| id | msgid | nec | dt  |
++---+-+-+
| 10 | 1 | 300 | 2008-06-25 09:18:05 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

And to get the counts, I can use:
mysql select nec, count(nec) as count from (select * from (select * 
from fake order by id desc) as fake1 group by msgid) as fake2 group by 
nec;

+-+---+
| nec | count |
+-+---+
| 300 | 4 |
| 305 | 2 |
+-+---+

So on my tiny test table, the logic is valid to get the results I 
want.  However, on my actual table with several million lines, the 
nested selects makes this a pretty ugly option (to the point even 
explain took a few minutes to respond).  What can I do to optimize 
this  query?


Thanks,
Kip Turk




No virus found in this incoming message.
Checked by AVG. 
Version: 8.0.101 / Virus Database: 270.4.1/1518 - Release Date: 6/25/2008 9:46 AM
  

Re: subselect logic

[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-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 fake order by msgid, id desc;
++---+-+-+
| id | msgid | nec | dt  |
++---+-+-+
| 10 | 1 | 300 | 2008-06-25 09:18:05 |
|  9 | 1 | 301 | 2008-06-25 09:18:02 |
|  6 | 1 | 305 | 2008-06-25 09:15:40 |
|  5 | 1 | 301 | 2008-06-25 09:15:32 |
|  2 | 1 | 301 | 2008-06-25 09:15:10 |
|  1 | 1 | 300 | 2008-06-25 09:15:04 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  8 | 2 | 305 | 2008-06-25 09:17:49 |
|  4 | 2 | 305 | 2008-06-25 09:15:19 |
|  3 | 2 | 301 | 2008-06-25 09:15:14 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 13 | 5 | 301 | 2008-06-25 09:23:33 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

I'm trying to grab and count the nec for the highest id entry for each 
distinct msgid.  To get the correct entries, I can use:


mysql select * from (select * from fake order by id desc) as fake1 
group by msgid;

++---+-+-+
| id | msgid | nec | dt  |
++---+-+-+
| 10 | 1 | 300 | 2008-06-25 09:18:05 |
| 11 | 2 | 300 | 2008-06-25 09:18:13 |
|  7 | 3 | 305 | 2008-06-25 09:17:44 |
| 12 | 4 | 300 | 2008-06-25 09:23:22 |
| 14 | 5 | 305 | 2008-06-25 09:23:39 |
| 15 | 6 | 300 | 2008-06-25 09:23:45 |
++---+-+-+

And to get the counts, I can use:
mysql select nec, count(nec) as count from (select * from (select * 
from fake order by id desc) as fake1 group by msgid) as fake2 group by 
nec;

+-+---+
| nec | count |
+-+---+
| 300 | 4 |
| 305 | 2 |
+-+---+

So on my tiny test table, the logic is valid to get the results I 
want.  However, on my actual table with several million lines, the 
nested selects makes this a pretty ugly option (to the point even 
explain took a few minutes to respond).  What can I do to optimize 
this  query?


Thanks,
Kip Turk




No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1518 - 
Release Date: 6/25/2008 9:46 AM
  





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Select mit Subselect Problem

[Marcus Füg]

Hallo,

folgendes Szenario:

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 gemacht, nur macht jetzt 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='$Kassenbon' AND  
Haendler='$Haendler' AND Datum='$Datum'


Danach die Ergebnisse aufsummiert.


Geht das effizienter?
























--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select mit Subselect Problem

[Kevin Hunter]

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 gemacht, nur macht jetzt bei 5 Mio
 Datensätze in den Abverkäufen PHP schlapp. DArum meine Frage kann ich
 effizienter Mit MySQL machen?

 [snip]

 Danach die Ergebnisse aufsummiert.
 
 Geht das effizienter?

1. This list is largely an English list, so you may have better luck
asking your question in English.  Alternatively, you might ask your
question on this list: http://lists.mysql.com/mysql-de

2. How can you more efficiently use MySQL in this scenario?  It depends
on exactly what you're trying to do.  If you can describe the problem
rather than exactly what you're trying to do, we may be able to better help.

That said, I'm guessing you're looking for GROUP BY and ORDER BY.  Take
a gander at the MySQL docs for the version that you are using.  A
starting point:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html

Kevin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Issue with SubSelect Quey

[leeladharan acharya]

Hi,

In MySQL, The following Query

SELECT `post`.`userid` , `post`.`pagetext` , `post`.`dateline`
,`post`.`threadid` , `thread`.`title` , `thread`.`forumid` FROM `post`LEFT
JOIN `thread` ON `post`.`threadid`=`thread`.`threadid` WHERE`userid`=`xyz`
AND `thread`.forumid not in(SELECT `childlist` 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 values gives the
expected result set...

Is this problem with My Query or Something else.. ?

Regards
Leelu

Re: Issue with SubSelect Quey

[Peter Brawley]

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 backwards. Isn't your model like this...

 a forum may have threads,
 a thread may have posts
?

Then wouldn't your query be...

SELECT
 post.userid , post.pagetext , post.dateline , post.threadid,
 thread.title , thread.forumid
FROM forum
LEFT JOIN thread ON thread.forumid = forum.childlist
LEFT JOIN post ON thread.threadid=post.threadid
WHERE forum.userid  xyz
 AND post.visible=1
 AND thread.visible = 1
ORDER BY post.dateline DESC LIMIT 5;

Or do I misunderstand your query?

PB


leeladharan acharya wrote:

Hi,

In MySQL, The following Query

SELECT `post`.`userid` , `post`.`pagetext` , `post`.`dateline`
,`post`.`threadid` , `thread`.`title` , `thread`.`forumid` FROM 
`post`LEFT
JOIN `thread` ON `post`.`threadid`=`thread`.`threadid` 
WHERE`userid`=`xyz`

AND `thread`.forumid not in(SELECT `childlist` 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 values gives the
expected result set...

Is this problem with My Query or Something else.. ?

Regards
Leelu



No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.472 / Virus Database: 269.8.7/830 - Release Date: 6/3/2007 12:47 PM
  

Re: View with Subselect for User ID

[Christian High]

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
SECURITY DEFINER VIEW `shared_v` AS select `Shared`.`ID` AS
`ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE (`Shared`.`UserID`
= (SELECT UserID FROM Users WHERE Login =
convert(substring_index(user(),_utf8'@',1) using latin1));

However, MySQL doesn't gulp the subquery for the ID.
I don't want to have a VARCHAR column with the user name in this table,
because it can easily
grow very large.
Would JOINS be the way to go?
If so, could anyone please give me an example of how to accomplish this
with joins?

I would appreciate any tip.

Thank you in advance,
-Andy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





From what i can tell from your create statement I think a join will

get you what you want. Try this--
SELECT Shared.ID, Shared.RawID
FROM Shared
JOIN Users ON
(Shared.ID = Users.UserID)
WHERE Users.Login = convert(substring_index(user(),_utf8'@', 1) using latin1);

If that doesn't get you what you are looking for than i have
misunderstood your requirements

CJ

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: View with Subselect for User ID

[Andreas Iwanowski]

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
Users.UserID to Shared.OwnerID, but that produced an error.

Maybe that is confusing, but basically I wish have the user only see
data where:
1. His ID is in the UserID field of the row OR
2. His ID is in the OwnerID field of the row OR
3. His data from the Users table references an ID in the Groups table
that is is the Shared table's GroupID field

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 `GroupID`
from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name
= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN
Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID)
WHERE Users.ID = Shared.OwnerID;


That failed with multiple errors, the first one occuring at the AND
JOIN. Apparently I cannot have multiple JOINS in one statement?
Please apologize my limited knowledge of JOINS.

I would appreciate if you could take the time to have a look at this.
Thank you very much!,
  Andy




From what i can tell from your create statement I think a join will
get you what you want. Try this-- SELECT Shared.ID, Shared.RawID FROM
Shared JOIN Users ON (Shared.ID = Users.UserID) WHERE Users.Login =
convert(substring_index(user(),
_utf8'@', 1) using latin1);

If that doesn't get you what you are looking for than i have
misunderstood your requirements

CJ

 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` AS 
 `ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE 
 (`Shared`.`UserID` = (SELECT UserID FROM Users WHERE Login =
 convert(substring_index(user(),_utf8'@',1) using latin1));

 However, MySQL doesn't gulp the subquery for the ID.
 I don't want to have a VARCHAR column with the user name in this 
 table, because it can easily grow very large.
 Would JOINS be the way to go?
 If so, could anyone please give me an example of how to accomplish 
 this with joins?

 I would appreciate any tip.

 Thank you in advance,
 -Andy

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: View with Subselect for User ID

[Martijn Tonies]

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 `GroupID`
from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name
= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN
Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID)
WHERE Users.ID = Shared.OwnerID;


That failed with multiple errors, the first one occuring at the AND
JOIN. Apparently I cannot have multiple JOINS in one statement?

Sure you can, but you might want to read up on your SQL.
http://www.w3schools.com/sql/default.asp

Please apologize my limited knowledge of JOINS.

If you take a look at the MySQL documentation, you can see
there's a clear way of creating SQL statements.

Basically:

[select clause]
[from clause]
[where clause]
[group by clause]
[order by clause]

( I'm not using the correct notation here, but some of these
are optional )

Now, a FROM clause can consist of multiple tables, including
multiple JOINs, each JOIN is following by a join-match-clause
(which is the ON part of the JOIN).

FROM myfirsttable t1 JOIN mysecondtable t2 ON t1.id = t2.foreignid
JOIN mythirdtable t3 ON t1.id = t3.id

etc... This will establish how these tables relate to eachother.

In the WHERE clause, you will write your row filtering items, eg:

WHERE t1.myuserid = 5
AND t2.mystatus = 'CONFIRMED'



Now, try and figure out your own SQL statement :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: View with Subselect for User ID

[Andreas Iwanowski]

 Thank you for the clarification!

For some reason I believed the WHERE belonged to the specific JOIN
clause.
I came up with a clause, but I removed the Group part, for I didn't know
how to do that.
I will work on that later.

Would this statement be good SQL practice?

---
CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL
SECURITY DEFINER VIEW `shared_v`
   AS select `shared`.`ID` AS `ID`,`shared`.`RawID` AS
`RawID`,`shared`.`Added` AS `Added`,
 `shared`.`Keywords` AS `Keywords`,`shared`.`OwnerID` AS
`OwnerID`,`shared`.`UserID` AS `UserID`
FROM (`shared`
  LEFT JOIN `users` on((`shared`.`UserID` = `users`.`ID`)))
WHERE (`users`.`Name` = convert(substring_index(user(),_utf8'@',1) using
latin1))
   OR (`Shared`.`OwnerID` = (SELECT ID FROM `Users` WHERE `Name` =
convert(substring_index(user(),_utf8'@',1) using latin1)));
---

-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 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 `GroupID`

from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name

= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN 
Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID)

WHERE Users.ID = Shared.OwnerID;


That failed with multiple errors, the first one occuring at the AND 
JOIN. Apparently I cannot have multiple JOINS in one statement?

Sure you can, but you might want to read up on your SQL.
http://www.w3schools.com/sql/default.asp

Please apologize my limited knowledge of JOINS.

If you take a look at the MySQL documentation, you can see there's a
clear way of creating SQL statements.

Basically:

[select clause]
[from clause]
[where clause]
[group by clause]
[order by clause]

( I'm not using the correct notation here, but some of these are
optional )

Now, a FROM clause can consist of multiple tables, including multiple
JOINs, each JOIN is following by a join-match-clause (which is the ON
part of the JOIN).

FROM myfirsttable t1 JOIN mysecondtable t2 ON t1.id = t2.foreignid JOIN
mythirdtable t3 ON t1.id = t3.id

etc... This will establish how these tables relate to eachother.

In the WHERE clause, you will write your row filtering items, eg:

WHERE t1.myuserid = 5
AND t2.mystatus = 'CONFIRMED'



Now, try and figure out your own SQL statement :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

View with Subselect for User ID

[Andreas Iwanowski]

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` AS
`ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE (`Shared`.`UserID`
= (SELECT UserID FROM Users WHERE Login =
convert(substring_index(user(),_utf8'@',1) using latin1));

However, MySQL doesn't gulp the subquery for the ID.
I don't want to have a VARCHAR column with the user name in this table,
because it can easily
grow very large.
Would JOINS be the way to go?
If so, could anyone please give me an example of how to accomplish this
with joins?

I would appreciate any tip.

Thank you in advance,
-Andy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with subselect and primary keys

[Derek Fountain]

Can someone tell me what's wrong with this test:


create table t1 ( course_id int(10) signed not null,   primary key
(course_id) );
create table t2 ( course_id int(10) unsigned not null, primary key
(course_id) );

insert into t1 values
(1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(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
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 '1'.

Can anyone explain what's going on?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with subselect and primary keys

[Dan Buettner]

Derek, I was able to replicate all the behaviors you describe in 5.0.21.

I noticed you have a signed INT in one table and an UNsigned INT in
the other.  I changed t1 to UNsigned and then the query returns the
results you would expect:

+---+
| course_id |
+---+
|-2 |
|-1 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
|68 |
+---+

seems like the signed/unsigned data is not being converted before
comparison, perhaps.  If you can't change your column type in the
table, perhaps you could use the CAST function in your queries?

HTH,
Dan


On 9/29/06, Derek Fountain [EMAIL PROTECTED] wrote:

Can someone tell me what's wrong with this test:


create table t1 ( course_id int(10) signed not null,   primary key
(course_id) );
create table t2 ( course_id int(10) unsigned not null, primary key
(course_id) );

insert into t1 values
(1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(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
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 '1'.

Can anyone explain what's going on?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect application

[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 with a coworker
about a reasonable application of subselects vs. a WHERE clause or table
join.

Thank you in advance.
--
Chris White
Interfuel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Subselect application

[Chris White]

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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

unexpected EXPLAIN result with subselect

[Matthias Pigulla]

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 AS t1
WHERE t1.id
IN (
-- Subquery
SELECT DISTINCT data_id
FROM wfd_reference
WHERE wfd_field_id =699
AND ref_data_id
IN ( 171 )
)

This gives:

*** 1. row ***
   id: 1
  select_type: PRIMARY
table: t1
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 277
Extra: Using where
*** 2. row ***
   id: 2
  select_type: DEPENDENT SUBQUERY
table: wfd_reference
 type: ref
possible_keys: field_data,test
  key: field_data
  key_len: 4
  ref: const,func
 rows: 4
Extra: Using where; Using temporary

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.

EXPLAIN SELECT DISTINCT data_id
FROM wfd_reference
WHERE wfd_field_id =699
AND ref_data_id
IN ( 171 ) 

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: wfd_reference
 type: ref
possible_keys: field_data,test
  key: test
  key_len: 4
  ref: const,const
 rows: 9
Extra: Using where; Using temporary

Now if I just take the result of this query, concat the data_ids on the
application level and build the superquery as follows:

-- two-staged superquery variant
EXPLAIN SELECT t1.id, t1.name
FROM document AS t1
WHERE t1.id
IN (
32, 31, 30, 53, 56, 57, 58, 59, 60, 111
)

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: t1
 type: range
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 2
  ref: NULL
 rows: 10
Extra: Using where

the results seem to be much better. 

I was afraid of the dependent subquery for the ALL scan of t1, as t1
will become huge. OTOH, the subquery will be very restrictive: The
number of data_ids will always be very small, at least compared to the
number of rows in t1.

Are there any non-obvious reasons for the behaviour described above? Is
that something that cannot be optimized right now? Am I too
short-sighted with my optimization approach?

Thanks a lot,
Matthias


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unexpected EXPLAIN result with subselect

[Felix Geerinckx]

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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unexpected EXPLAIN result with subselect

[Gleb Paharenko]

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 now - the table definitions and

 sample data for brevity.

 

 -- Superquery

 EXPLAIN SELECT t1.id, t1.name

 FROM document AS t1

 WHERE t1.id

 IN (

-- Subquery

SELECT DISTINCT data_id

FROM wfd_reference

WHERE wfd_field_id =3D699

AND ref_data_id

IN ( 171 )

 )

 

 This gives:

 

 *** 1. row ***

   id: 1

  select_type: PRIMARY

table: t1

 type: ALL

 possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

 rows: 277

Extra: Using where

 *** 2. row ***

   id: 2

  select_type: DEPENDENT SUBQUERY

table: wfd_reference

 type: ref

 possible_keys: field_data,test

  key: field_data

  key_len: 4

  ref: const,func

 rows: 4

Extra: Using where; Using temporary

 

 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.

 

 EXPLAIN SELECT DISTINCT data_id

 FROM wfd_reference

 WHERE wfd_field_id =3D699

 AND ref_data_id

 IN ( 171 )=20

 

 *** 1. row ***

   id: 1

  select_type: SIMPLE

table: wfd_reference

 type: ref

 possible_keys: field_data,test

  key: test

  key_len: 4

  ref: const,const

 rows: 9

Extra: Using where; Using temporary

 

 Now if I just take the result of this query, concat the data_ids on the

 application level and build the superquery as follows:

 

 -- two-staged superquery variant

 EXPLAIN SELECT t1.id, t1.name

 FROM document AS t1

 WHERE t1.id

 IN (

 32, 31, 30, 53, 56, 57, 58, 59, 60, 111

 )

 

 *** 1. row ***

   id: 1

  select_type: SIMPLE

table: t1

 type: range

 possible_keys: PRIMARY

  key: PRIMARY

  key_len: 2

  ref: NULL

 rows: 10

Extra: Using where

 

 the results seem to be much better.=20

 

 I was afraid of the dependent subquery for the ALL scan of t1, as t1

 will become huge. OTOH, the subquery will be very restrictive: The

 number of data_ids will always be very small, at least compared to the

 number of rows in t1.

 

 Are there any non-obvious reasons for the behaviour described above? Is

 that something that cannot be optimized right now? Am I too

 short-sighted with my optimization approach?

 

 Thanks a lot,

 Matthias

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: unexpected EXPLAIN result with subselect

[Michael Stassen]

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 t1.id, t1.name
FROM document AS t1
WHERE t1.id
IN (
-- Subquery
SELECT DISTINCT data_id
FROM wfd_reference
WHERE wfd_field_id =699
AND ref_data_id
IN ( 171 )
)

This gives:

*** 1. row ***
   id: 1
  select_type: PRIMARY
table: t1
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 277
Extra: Using where
*** 2. row ***
   id: 2
  select_type: DEPENDENT SUBQUERY
table: wfd_reference
 type: ref
possible_keys: field_data,test
  key: field_data
  key_len: 4
  ref: const,func
 rows: 4
Extra: Using where; Using temporary

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.

EXPLAIN SELECT DISTINCT data_id
FROM wfd_reference
WHERE wfd_field_id =699
AND ref_data_id
IN ( 171 ) 


*** 1. row ***
   id: 1
  select_type: SIMPLE
table: wfd_reference
 type: ref
possible_keys: field_data,test
  key: test
  key_len: 4
  ref: const,const
 rows: 9
Extra: Using where; Using temporary

Now if I just take the result of this query, concat the data_ids on the
application level and build the superquery as follows:

-- two-staged superquery variant
EXPLAIN SELECT t1.id, t1.name
FROM document AS t1
WHERE t1.id
IN (
32, 31, 30, 53, 56, 57, 58, 59, 60, 111
)

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: t1
 type: range
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 2
  ref: NULL
 rows: 10
Extra: Using where

the results seem to be much better. 


I was afraid of the dependent subquery for the ALL scan of t1, as t1
will become huge. OTOH, the subquery will be very restrictive: The
number of data_ids will always be very small, at least compared to the
number of rows in t1.

Are there any non-obvious reasons for the behaviour described above? Is
that something that cannot be optimized right now? Am I too
short-sighted with my optimization approach?

Thanks a lot,
Matthias


As others have already pointed out, mysql often doesn't optimize subqueries 
properly, and fixing that doesn't seem to be a high priority right now. 
Subqueries are relatively new in mysql, so it probably shouldn't be 
surprising that they aren't optimized as well as other things.  Fortunately, 
most subqueries can be rewritten as joins 
http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html, which mysql 
does a good job of optimizing.


I believe your query is equivalent to

  SELECT DISTINCT t1.id, t1.name
  FROM document AS t1
  JOIN wfd_reference AS wfd ON t1.id = wfd.data_id
  WHERE wfd.wfd_field_id = 699
AND wfd.ref_data_id IN (171);

which should perform better than the (incorrectly) dependent subquery, 
because it will be properly optimized.  Mysql will use the 'test' index in 
wfd_reference to pick the rows which match the conditions, then use t1's 
primary key to get the corresponding rows from t1.


I notice the DISTINCT in your subquery.  I take it that there may be 
multiple rows in wfd_reference with the same data_id, wfd_field_id = 699, 
and ref_data_id IN (171)?  (If not, just leave out the DISTINCT in the 
above.)  In that case, it may be possible that your 2-step approach will be 
even faster than the JOIN with DISTINCT.  You can do this directly in mysql, 
instead of in your app, by using a temporary table to store the inner query 
result.  Something like


  CREATE TEMPORARY TABLE matches
SELECT DISTINCT data_id
FROM wfd_reference
WHERE wfd_field_id = 699
  AND ref_data_id IN (171);

  SELECT t1.id, t1.name
  FROM document AS t1
  JOIN matches ON t1.id = matches.data_id;

  DROP TABLE matches;

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

very slow subselect on large innoDB table

[mathias brandt]

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.



the statement:

SELECT id from table1 WHERE id IN ( SELECT id FROM table1 WHERE cityname = 'bla 
)

still returns 0 rows but takes 12.9 seconds!!



does anyone know why it takes so long and if there is anything i can do?



thank you for your help, i really appreciate it. 

Re: very slow subselect on large innoDB table

[Joerg Bruehe]

Hi!


mathias brandt wrote:

[[...]]

the statement
SELECT id FROM table1 WHERE cityname = 'bla' (cityname has been indexed) 
takes 0.0002 seconds and returns 0 rows, which was expected.


the statement:
SELECT id from table1 WHERE id IN ( SELECT id FROM table1 WHERE cityname = 'bla 
)
still returns 0 rows but takes 12.9 seconds!!


The first statement uses an equality condition for a single on an index, 
the fastest thing possible.


The second statement can do the same in its subquery. But then, it gets 
(possibly) a list of values returned, and for such a list there are two 
general possibilities:
a) Loop over each element in the list, comparing it to id in the outer 
SELECT;
b) Scan the table for the outer SELECT, comparing id to the values in 
the list.


If id is not supported by an index, strategy a) is impossible.

Assuming that the table has got an index for id (which was not said), 
strategy a) might be the more effective for short returned lists - but 
in general, it will not be.
An IN condition is (semantically) equivalent to an OR expression 
which is notorious hard to optimize.


Mathias, if you want to pursue this further, you should at least specify 
the version 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 cityname there may be multiple id 
values, and the execution plan has to allow for that. Only if you can 
guarantee there is only one id, try to replace the IN by a =.


HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Server hangs and table gets corrupted on simple subselect

[Gleb Paharenko]

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.









Reitsma, Rene - COB [EMAIL PROTECTED] wrote:

 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=20

 MySQL Ver 14.7 Distrib 4.1.12 (Fedora Core 4)

 

 mysqldesc action

 +-+--+--+-+-++

 | Field   | Type | Null | Key | Default | Extra  |

 +-+--+--+-+-++

 | id  | int(11)  |  | PRI | NULL| auto_increment |

 | host_ip | varchar(16)  |  | | ||

 | file| varchar(255) |  | | ||

 | querystring | varchar(255) | YES  | | NULL||

 | timestamp   | datetime | YES  | | NULL||

 +-+--+--+-+-++

 5 rows in set (0.00 sec)

 

 From this table, I must delete all records associated with host_ips that

 occur only once in the table (all unique host_ips).

 

 I have tried the following approaches; all of which hang the server and

 corrupt the table:

 

 Method 1: First create a 'totals' table that holds for each host_ip the

 number of occurrences in the 'action' table:

 

 mysql create table totals as select host_ip, count(*) as hits

   from action

   group by host_ip

   order by hits;

 

 Next, combine the tables in a query (a 'select' for now, but a 'delete'

 eventually):

 

 mysql select from action where host_ip in

   (

 select host_ip

 from totals

 where hits =3D 1

   );

 

 Method 2: use an explicit join:

 

 mysql select host_ip

   from action, totals

   where action.host_ip =3D totals.host_ip

   and totals.hits =3D 1;

 

 Method 3: don't use the 'totals' table at all:

 

 mysql select host_ip

   from action

   group by host_id having count(*) =3D 1;

 

 As mentioned, none of these work and all of these hang the server and

 break the database.

 

 How do I do this?

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Server hangs and table gets corrupted on simple subselect

[Reitsma, Rene - COB]

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
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| id  | int(11)  |  | PRI | NULL| auto_increment |
| host_ip | varchar(16)  |  | | ||
| file| varchar(255) |  | | ||
| querystring | varchar(255) | YES  | | NULL||
| timestamp   | datetime | YES  | | NULL||
+-+--+--+-+-++
5 rows in set (0.00 sec)

From this table, I must delete all records associated with host_ips that
occur only once in the table (all unique host_ips).

I have tried the following approaches; all of which hang the server and
corrupt the table:

Method 1: First create a 'totals' table that holds for each host_ip the
number of occurrences in the 'action' table:

mysql create table totals as select host_ip, count(*) as hits
   from action
   group by host_ip
   order by hits;

Next, combine the tables in a query (a 'select' for now, but a 'delete'
eventually):

mysql select from action where host_ip in
   (
 select host_ip
 from totals
 where hits = 1
   );

Method 2: use an explicit join:

mysql select host_ip
   from action, totals
   where action.host_ip = totals.host_ip
   and totals.hits = 1;

Method 3: don't use the 'totals' table at all:

mysql select host_ip
   from action
   group by host_id having count(*) = 1;

As mentioned, none of these work and all of these hang the server and
break the database.

How do I do this?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[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
you would still run the risk of an executor error when the subquery
returns more then one record.

Try this:
UPDATE table1 a, table2 b
SET a.field1 = b.field1
WHERE b.field2 = 'Some Value'
AND a.field2  = 'Another Value'

Jochem

PS Please use single quotes to delimit strings, sticking to the SQL
standard makes it easier to read.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Subselect in an Update query

[Ed Reed]

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

Re: Subselect in an Update query

[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 it wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Subselect in an Update query

[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

Update table1 Set field1=(Select field1 From t Where field2=Some
 Value)
 Where field2  =Another Value;

**
mysql select * from upd;
+--+--+
| a| b|
+--+--+
|1 | one  |
|1 | two  |
|2 | one  |
|2 | two  |
|1 | Un   |
+--+--+
5 rows in set (0.09 sec)

mysql create table t as select * from upd where b='Un';
mysql update upd set a=(select a from t where b='Un') where b='one';
mysql select * from upd;
+--+--+
| a| b|
+--+--+
|1 | one  |
|1 | two  |
|1 | one  |  changed
|2 | two  |
|1 | Un   |
+--+--+
5 rows in set (0.00 sec)



Mathias



Selon Ed Reed [EMAIL PROTECTED]:

 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





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[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 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | GHI   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+
 



 Update table1 Set field1=(Select field1 From table1 Where field2=Some Value)
 Where field2 =Another Value;

This query should set FIELD1 of Record 3 to 'DEF'
 
+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | DEF   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+




 
That's it!  Should be easy but I get an error that says You can't specify 
target table 'table1' for update in FROM clause
 
- Thanks


 [EMAIL PROTECTED] 6/17/05 11:03:40 AM 
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 wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Subselect in an Update query

[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,


+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | GHI   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+




 


Update table1 Set field1=(Select field1 From table1 Where field2=Some Value)
Where field2 =Another Value;
   



This query should set FIELD1 of Record 3 to 'DEF'

+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | DEF   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+





That's it!  Should be easy but I get an error that says You can't specify target 
table 'table1' for update in FROM clause

- Thanks


 


[EMAIL PROTECTED] 6/17/05 11:03:40 AM 
   


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 wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.


Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[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 Select field1 From table1 Where field2=Some
Value

Update table1 Set field1=(Select field1 From t Where field2=Some
Value)
Where field2 =Another Value;

**
mysql select * from upd;
+--+--+
| a | b |
+--+--+
| 1 | one |
| 1 | two |
| 2 | one |
| 2 | two |
| 1 | Un |
+--+--+
5 rows in set (0.09 sec)

mysql create table t as select * from upd where b='Un';
mysql update upd set a=(select a from t where b='Un') where b='one';
mysql select * from upd;
+--+--+
| a | b |
+--+--+
| 1 | one |
| 1 | two |
| 1 | one | changed
| 2 | two |
| 1 | Un |
+--+--+
5 rows in set (0.00 sec)



Mathias



Selon Ed Reed  [EMAIL PROTECTED] :

 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





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 

Re: Subselect in an Update query

[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 same field but from a 
different record of the same table. 
For example, using the query in my original message,
 
+--+
|RecID |FIELD1 | FIELD2 |
+--+
| 1 | ABC | A Value |
+--+
| 2 | DEF | Some Value |
+--+
| 3 | GHI | Another Value |
+--+
| 4 | JKL | More Values |
+--+
 



 

Update table1 Set field1=(Select field1 From table1 Where field2=Some Value)
Where field2 =Another Value;
 


This query should set FIELD1 of Record 3 to 'DEF'
 
+--+
|RecID |FIELD1 | FIELD2 |
+--+
| 1 | ABC | A Value |
+--+
| 2 | DEF | Some Value |
+--+
| 3 | DEF | Another Value |
+--+
| 4 | JKL | More Values |
+--+




 
That's it! Should be easy but I get an error that says You can't specify 
target table 'table1' for update in FROM clause
 
- Thanks


 

 [EMAIL PROTECTED]  6/17/05 11:03:40 AM 
 

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 wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 

concat multirow subselect

[Stano Paska]

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');
insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789');

I need display table aaa with last column concat values from bbb. Like this:
| 1 | 'aaa' | '123 456' |
| 2 | 'bbb' | '789' |
It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use 
stored procedures?

Stano.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: concat multirow subselect

[Michael Stassen]

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, 'aaa'), (2, 'bbb');
insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789');

I need display table aaa with last column concat values from bbb. Like 
this:
| 1 | 'aaa' | '123 456' |
| 2 | 'bbb' | '789' |

It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use 
stored procedures?

Stano.
In general, you can simply have your app output a newline, id, and title 
only when the id changes.

Starting with 4.1, you can get the same result using GROUP_CONCAT(). 
See the manual for the details 
http://dev.mysql.com/doc/mysql/en/group-by-functions.html.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: concat multirow subselect

[Stano Paska]

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 into aaa values (1, 'aaa'), (2, 'bbb');
insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789');

I need display table aaa with last column concat values from bbb. 
Like this:
| 1 | 'aaa' | '123 456' |
| 2 | 'bbb' | '789' |

It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use 
stored procedures?

Stano.

In general, you can simply have your app output a newline, id, and 
title only when the id changes.

Starting with 4.1, you can get the same result using GROUP_CONCAT(). 
See the manual for the details 
http://dev.mysql.com/doc/mysql/en/group-by-functions.html.

Michael
Yes, this is what I need. I didn't read this part of manual. :-(
Thank you.
S.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Convert subselect query to pre-subselect query

[Ed Lazor]

Hi Everyone,

I got excited when I discovered subselects, but quickly discovered that 4.1
is still in gamma and I can't put it onto 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:

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 the same
result?

Here's what I was attempting, in case it's close, but it's erroring out:

select ID from products right join categories where (categories.ID = '21' or
categories.ParentID='21') AND (products.CategoryID = categories.ID or
products.CategoryID = categories.ParentID)

Thanks,

Ed


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Convert subselect query to pre-subselect query

[Remi Mikalsen]

Try this...

select a.ID 
from products a, categories b
where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21')

The query is pretty straigthforward and I believe it's quite easy to understand. Hope 
this is 
what you wanted!

A tip... only use left and right joins whenever you want what is on the left or 
right side of 
the join in your results, even if you can't get any matches on the opposite side. 
For 
example (I'll try to make it simple!): imagine you have a Video Club.You have movies, 
and 
you have loans. Normally, movies in one table (the left table in this example) and 
loans in 
different table (the right table). Now, imagine you would like to print a list of ALL 
movies in 
your database, and ALSO list all loans for each movie. You could do a LEFT JOIN 
similar to 
this: select movies.*, loans.* from movies left join loans on (movies.id = 
loans.movieid) order 
by movies.title, loans.loan_date desc. The result would be a list with ALL movies in 
your 
database, ordered by the movie title. Each title would appear once for every loan; the 
loans 
would be ordered within the movie title, last first. Now, here is the particularity 
with LEFT 
JOINS: whenever a movie has NEVER been on loan, it will not be possible to get any 
values 
from the loans table. With an INNER JOIN (the type of join I suggest for your 
problem), the 
movie would be omitted in the query result, but with a LEFT JOIN, the movie and all 
its 
columns will appear once - but the loans columns corresponding to that movie would 
appear 
all NULL. Got it? hope I explained more or less well... 

Honestly, I almost never use LEFT and RIGHT joins, thus, the syntax MIGHT be wrong (I 
was too lazy to check in the manual :) ) - I'm not a pro in MySQL, so if I explained 
something 
wrong, I hope the more experienced members will correct it.



Remi Mikalsen

E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com



On 8 Oct 2004 at 16:30, Ed Lazor wrote:

 Hi Everyone,
 
 I got excited when I discovered subselects, but quickly discovered that 4.1
 is still in gamma and I can't put it onto 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:
 
 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 the same
 result?
 
 Here's what I was attempting, in case it's close, but it's erroring out:
 
 select ID from products right join categories where (categories.ID = '21' or
 categories.ParentID='21') AND (products.CategoryID = categories.ID or
 products.CategoryID = categories.ParentID)
 
 Thanks,
 
 Ed
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Convert subselect query to pre-subselect query

[Ed Lazor]

Thanks Remi =)

I just ran a test and it worked.  Honestly, I'm still a little stumped on
why it works, but I'll keep playing with it for a while to see if I can get
it.  I'll either eventually figure it out or come back in frustration asking
for more help ;) hehe



 -Original Message-
 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')
 
 The query is pretty straigthforward and I believe it's quite 
 easy to understand. Hope this is 
 what you wanted!
 
 A tip... only use left and right joins whenever you want what 
 is on the left or right side of 
 the join in your results, even if you can't get any matches 
 on the opposite side. For 
 example (I'll try to make it simple!): imagine you have a 
 Video Club.You have movies, and 
 you have loans. Normally, movies in one table (the left table 
 in this example) and loans in 
 different table (the right table). Now, imagine you would 
 like to print a list of ALL movies in 
 your database, and ALSO list all loans for each movie. You 
 could do a LEFT JOIN similar to 
 this: select movies.*, loans.* from movies left join loans on 
 (movies.id = loans.movieid) order 
 by movies.title, loans.loan_date desc. The result would be a 
 list with ALL movies in your 
 database, ordered by the movie title. Each title would appear 
 once for every loan; the loans 
 would be ordered within the movie title, last first. Now, 
 here is the particularity with LEFT 
 JOINS: whenever a movie has NEVER been on loan, it will not 
 be possible to get any values 
 from the loans table. With an INNER JOIN (the type of join I 
 suggest for your problem), the 
 movie would be omitted in the query result, but with a LEFT 
 JOIN, the movie and all its 
 columns will appear once - but the loans columns 
 corresponding to that movie would appear 
 all NULL. Got it? hope I explained more or less well... 
 
 Honestly, I almost never use LEFT and RIGHT joins, thus, the 
 syntax MIGHT be wrong (I 
 was too lazy to check in the manual :) ) - I'm not a pro in 
 MySQL, so if I explained something 
 wrong, I hope the more experienced members will correct it.
 
 
 
 Remi Mikalsen
 
 E-Mail:   [EMAIL PROTECTED]
 URL:  http://www.iMikalsen.com
 
 
 
 On 8 Oct 2004 at 16:30, Ed Lazor wrote:
 
  Hi Everyone,
  
  I got excited when I discovered subselects, but quickly 
 discovered that 4.1
  is still in gamma and I can't put it onto 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:
  
  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 the same
  result?
  
  Here's what I was attempting, in case it's close, but it's 
 erroring out:
  
  select ID from products right join categories where 
 (categories.ID = '21' or
  categories.ParentID='21') AND (products.CategoryID = 
 categories.ID or
  products.CategoryID = categories.ParentID)
  
  Thanks,
  
  Ed
  
  
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Convert subselect query to pre-subselect query

[Ed Lazor]

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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Bernhard Döbler]

Hi Victoria,

that's exactly what I needed. Works fine.
I know Subselects only from Intrbas/Firebird so far. I know that I can pass
a SELECT to the list of columns of a SELECT if it only returns one result
(one col, one row). So I thought/hoped/imagined/expected I can pass a SELECT
returning 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 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 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 not to pass a subselect
  to an aggregate function?
 

 If I've got you right you need:

 SELECT AVG(anz) FROM (SELECT COUNT(time) anz FROM table GROUP BY time) as
table1;



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Subselect in aggregate function in MySQL 4.1.1a-alpha

[programmer]

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 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 not to pass a subselect
to an aggregate function?

Best wishes,
Bernhard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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 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 not to pass a subselect
 to an aggregate function?
 

If I've got you right you need:

SELECT AVG(anz) FROM (SELECT COUNT(time) anz FROM table GROUP BY time) as 
table1;


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Subselect Problem

[Peter J. Krawetzky]

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
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect Problem

[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 on Redhat Linux 8.


Subqueries are not implemented until MySQL 4.1.



--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SUBSELECT QUESTION?

[Prashant Pai]

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 SalaryInfo WHERE
Salary=Max(Salary)

Would using MySQL 4.1 that has support for nested select help my case?

Thanks in advance
prashant

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SUBSELECT QUESTION?

[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 PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 24, 2003 12:00 PM
Subject: SUBSELECT QUESTION?


 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 SalaryInfo WHERE
 Salary=Max(Salary)

 Would using MySQL 4.1 that has support for nested select help my case?

 Thanks in advance
 prashant

 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MAX in a SubSelect

[Oscar (TOMCAT)]

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 do that ???

Thanks,

Oscar

PS. Above the sample script to create the tables needed to run this example query.

DROP TABLE VERSOES;
CREATE TABLE VERSOES
(
IDVERSAO CHAR(4),
DATA DATE
)
;
DROP TABLE AUDITORIAS;
CREATE TABLE AUDITORIAS
(
PROGRAMA CHAR(10),
AVALIACAO CHAR(2),
IDVERSAO CHAR(4)
)
;
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V001','2003-08-22');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V002','2003-08-23');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V003','2003-08-23');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V003');

Re: MAX in a SubSelect

[Leonardo Rodrigues Magalhes]

MySQL 4.0 (the current stable brench) does not support subqueries.
This feature is present in the beta brench of MySQL, v4.1.

http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Subqueries

Sincerily,
Leonardo Rodrigues

- Original Message - 
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 = (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 do that ???


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

subselect doesnt work

[Daniel Rossi]

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 = lj.locationID WHERE l.locationID NOT IN (select 
locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY l.location ASC

so when shotlistID is selected all the keys from the locations_join joined to the 
shotlistID would be remove from the locations list please help, i'm trying to do this 
in one query saving from getting all the keys into an array then checking if the 
values arent in the array when generating the list.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ranking student grade ? with subquery/subselect?

[marcello stanley]

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(*) FROM tblStudentGrades 
WHERE [studenttestmark][Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;
 
I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though :(
 
Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?
 
Or maybe I should have approach it differently?
 
Thanks in Advance
 
Regards,
Marcello s.
 
 
 

Re: Q: Delete subselect

[Egor Egorov]

[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 and size in both table_a and 
 table_b.
 

Retrieve data using programming language and then delete data in cycle.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Q: Delete subselect

[Tbird67ForSale]

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 and 
table_b.

Any help would be greatly appreciated.

Regards, 
Tony

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

[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 my Windows box today (running 4.1), 
and created a couple of tables. Here's the result of the session:

 --
desc table1
- --
+---+--+---+--+-+-+-
- ---+
| Field | Type | Collation | Null | Key | Default | Extra
|
+---+--+---+--+-+-+-
- ---+
| id| int(11)  | binary|  | PRI | NULL|
auto_increment |
| date1 | datetime | latin1_swedish_ci | YES  | | NULL|
|
+---+--+---+--+-+-+-
- ---+
2 rows in set (0.40 sec)

- --
desc table2
- --
+---+--+---+--+-+-+-
- ---+
| Field | Type | Collation | Null | Key | Default | Extra
|
+---+--+---+--+-+-+-
- ---+
| id| int(11)  | binary|  | PRI | NULL|
auto_increment |
| Date2 | datetime | latin1_swedish_ci | YES  | | NULL|
|
+---+--+---+--+-+-+-
- ---+
2 rows in set (0.00 sec)

- --
Select * from Table1
- --
++-+
| id | date1   |
++-+
|  1 | 2003-07-22 07:41:00 |
|  2 | 2003-07-21 07:41:00 |
|  3 | 2003-07-21 08:26:00 |
|  4 | 2003-07-28 08:26:00 |
++-+
4 rows in set (0.00 sec)

- --
Select * from table2
- --
++-+
| id | Date2   |
++-+
|  1 | 2003-07-22 07:41:00 |
|  2 | 2003-07-21 07:41:00 |
++-+
2 rows in set (0.00 sec)

- --
Select * from Table1
Where Date1 in (Select Date2 from Table2)
- --
++-+
| id | date1   |
++-+
|  1 | 2003-07-22 07:41:00 |
|  2 | 2003-07-21 07:41:00 |
++-+
2 rows in set (0.00 sec)

- ---
As you can see, 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 the entire session (including any error messages) for us to look at. 
If  the fechas table is large, only send us 4-5 rows so we can see it worked. 
Subselect works fine, so it's probably something small.

- ---Michael


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/HxuCjeziQOokQnARAlREAJ0eibYiMMu9J74cNX4ElozMWyqHxQCgrMlV
tDmsxAlgRvXELRSLt1lXvj8=
=M+jO
-END PGP SIGNATURE-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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 option in the command
 line to start the server, according to the manual this should enable the
 subselect feature, but it doesn’t, so I try with the 4.1 alpha win32
 version with the same error:

 RROR 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 and so on

 any ideas why I cant use the subselect feature? I missing something
 about the conf?? maybe my.ini??

I still haven't seen the query that has the error. Subselect does work in 4.1. 
If you'll post the query that has the error (and the the entire error 
message, the near is significant g) we'll be glad to help.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/HQxTjeziQOokQnARAp0xAJwOqpaWhSlXPNrpAz2bkRCfjFF2hQCeKB2C
ZrZn9QuI3MPDbXcqY0RDFeI=
=25tB
-END PGP SIGNATURE-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[mysql]

Hi again,
Sorry I completely  forget the query:
SELECT * FROM Kardex WHERE Fecha IN (SELECT maxfecha FROM fechas)
This is really weird cause I try the same query in SQL form M$ and it
work. Is a simple query and I know that can be rebuild with other query
syntax but the question here is why it doesn’t 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 this are the field-structure of my table:

Again thanks a lot for your help.
P.D. Im using 4.1 alpha version for win32

Kardex Table:
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| Nro   | bigint(20)  |  | PRI | 0   |   |
| Fecha | datetime| YES  | | NULL|   |
| CodProducto   | bigint(20)  | YES  | | 0   |   |
| Ref   | varchar(50) | YES  | | NULL|   |
| Empresa   | varchar(50) | YES  | | NULL|   |
| Cant  | double  | YES  | | 0   |   |
| Balance   | double  | YES  | | 0   |   |
| BalanceLitros | varchar(50) | YES  | | NULL|   |
| CantKilos | double  | YES  | | 0   |   |
| BalanceKilos  | double  | YES  | | 0   |   |
+---+-+--+-+-+---+

fechas table:
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| Nro   | bigint(20)  |  | PRI | 0   |   |
| maxfecha  | datetime| YES  | | NULL|   |
| CodProducto   | bigint(20)  | YES  | | 0   |   |
+---+-+--+-+-+---+


-Original Message-
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:
 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, but it doesn’t, so I try with the 4.1 alpha win32
 version with the same error:

 RROR 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 and so on

 any ideas why I cant use the subselect feature? I missing something
 about the conf?? maybe my.ini??

I still haven't seen the query that has the error. Subselect does work
in 4.1. 
If you'll post the query that has the error (and the the entire error 
message, the near is significant g) we'll be glad to help.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/HQxTjeziQOokQnARAp0xAJwOqpaWhSlXPNrpAz2bkRCfjFF2hQCeKB2C
ZrZn9QuI3MPDbXcqY0RDFeI=
=25tB
-END PGP SIGNATURE-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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 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. Please help!!!

You can't turn on subselects with --new option in 4.0.x versions. They are only works 
since 4.1.0.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[mysql]

No problem, this is:

Kardex Table:
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| Nro   | bigint(20)  |  | PRI | 0   |   |
| Fecha | datetime| YES  | | NULL|   |
| CodProducto   | bigint(20)  | YES  | | 0   |   |
| Ref   | varchar(50) | YES  | | NULL|   |
| Empresa   | varchar(50) | YES  | | NULL|   |
| Cant  | double  | YES  | | 0   |   |
| Balance   | double  | YES  | | 0   |   |
| BalanceLitros | varchar(50) | YES  | | NULL|   |
| CantKilos | double  | YES  | | 0   |   |
| BalanceKilos  | double  | YES  | | 0   |   |
+---+-+--+-+-+---+

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  Guitar Yamaha   2003-07-01
10  Nissan Car  2003-05-01
30  Stereo Cables   2003-01-20
20  Guitar Yamaha   2003-10-05
10  Nissan Car  2003-08-10
10  Nissan Car  2003-07-20
20  Guitar Yamaha   2003-09-10


So I have 3 groups, grouping by Cod 10,20,30 i need to build a query to
get the max date of every group, the result must be something like this

Cod Product date
10  Nissan Car  2003-08-10
20  Guitar Yamaha   2003-10-05
30  Stereo Cables   2003-01-20

I try the following query with no look
SELECT 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 option

-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. Please help!!!

Any chance you could post the table structure and the SQL statement that
got 
the error?


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/GeQBjeziQOokQnARAmG6AJ4p3dAjtUmNzuueHHAhtecsE0+/EACfSjlF
3qWktDP00+Biu82iTKnyeRw=
=RO1D
-END PGP SIGNATURE-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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
 20Guitar Yamaha   2003-10-05
 10Nissan Car  2003-08-10
 10Nissan Car  2003-07-20
 20Guitar Yamaha   2003-09-10


 So I have 3 groups, grouping by Cod 10,20,30 i need to build a query to
 get the max date of every group, the result must be something like this

 Cod   Product 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   | ItemDate   |
+--+---++
|   10 | Nissan Car| 2003-06-20 |
|   20 | Guitar Yamaha | 2003-07-01 |
|   10 | Nissan Car| 2003-05-01 |
|   30 | Stereo Cables | 2003-01-20 |
|   20 | Guitar Yamaha | 2003-10-05 |
|   10 | Nissan Car| 2003-08-10 |
|   10 | Nissan Car| 2003-07-20 |
|   20 | Guitar Yamaha | 2003-09-10 |
+--+---++

This matches the data you list above. The following query:

Select Cod, Product, Max(ItemDate) from Q1 Group By Cod;

Returns

+--+---+---+
| Cod  | Product   | Max(itemdate) |
+--+---+---+
|   10 | Nissan Car| 2003-08-10|
|   20 | Guitar Yamaha | 2003-10-05|
|   30 | Stereo Cables | 2003-01-20|
+--+---+---+

Which appears to be what your're looking for. This should work in any version 
of MySQL.

- ---Michael

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/HKc5jeziQOokQnARAq11AJkB60/rV+CylqxaK/qSa0Gj5Z5hzwCeONV6
hAuU4NW9+HOajJpenIdA5BY=
=unNS
-END PGP SIGNATURE-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[mysql]

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, but it doesn’t, so I try with the 4.1 alpha win32
version with the same error:

RROR 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 and so on

any ideas why I cant use the subselect 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-
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
 20Guitar Yamaha   2003-10-05
 10Nissan Car  2003-08-10
 10Nissan Car  2003-07-20
 20Guitar Yamaha   2003-09-10


 So I have 3 groups, grouping by Cod 10,20,30 i need to build a query
to
 get the max date of every group, the result must be something like
this

 Cod   Product 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   | ItemDate   |
+--+---++
|   10 | Nissan Car| 2003-06-20 |
|   20 | Guitar Yamaha | 2003-07-01 |
|   10 | Nissan Car| 2003-05-01 |
|   30 | Stereo Cables | 2003-01-20 |
|   20 | Guitar Yamaha | 2003-10-05 |
|   10 | Nissan Car| 2003-08-10 |
|   10 | Nissan Car| 2003-07-20 |
|   20 | Guitar Yamaha | 2003-09-10 |
+--+---++

This matches the data you list above. The following query:

Select Cod, Product, Max(ItemDate) from Q1 Group By Cod;

Returns

+--+---+---+
| Cod  | Product   | Max(itemdate) |
+--+---+---+
|   10 | Nissan Car| 2003-08-10|
|   20 | Guitar Yamaha | 2003-10-05|
|   30 | Stereo Cables | 2003-01-20|
+--+---+---+

Which appears to be what your're looking for. This should work in any
version 
of MySQL.

- ---Michael

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/HKc5jeziQOokQnARAq11AJkB60/rV+CylqxaK/qSa0Gj5Z5hzwCeONV6
hAuU4NW9+HOajJpenIdA5BY=
=unNS
-END PGP SIGNATURE-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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 manual this should enable the
subselect feature, but it doesn’t, so I try with the 4.1 alpha win32
version with the same error:
I'm curious where you find it in the manual that --new turns on
subselects in 4.0.12.
RROR 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 and so on
any ideas why I cant use the subselect feature? I missing something
about the conf?? maybe my.ini??
Perhaps it's simply that your query is malformed.  As far as I can
tell, you haven't actually posted the query, so it's diffficult to
tell.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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. Please help!!!

Any chance you could post the table structure and the SQL statement that got 
the error?


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQE/GeQBjeziQOokQnARAmG6AJ4p3dAjtUmNzuueHHAhtecsE0+/EACfSjlF
3qWktDP00+Biu82iTKnyeRw=
=RO1D
-END PGP SIGNATURE-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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?
 
 (This is on mysql version 4.1.0-alpha-max-nt.)
 
 First, here's the tables in question:
 
 mysql describe m;
 +---+-+---+--+-+-+---+
 | Field | Type| Collation | Null | Key | Default | Extra |
 +---+-+---+--+-+-+---+
 | toid  | int(11) | binary| YES  | | NULL|   |
 | rd| int(11) | binary| YES  | | NULL|   |
 +---+-+---+--+-+-+---+
 
 mysql describe p;
 +-+-+---+--+-+-+---+
 | Field   | Type| Collation | Null | Key | Default | Extra |
 +-+-+---+--+-+-+---+
 | userid  | int(11) | binary|  | PRI | 0   |   |
 | pmnew   | int(11) | binary| YES  | | NULL|   |
 | pmtotal | int(11) | binary| YES  | | NULL|   |
 +-+-+---+--+-+-+---+
 
 and the data in the tables...
 
 mysql select * from p;
 ++---+-+
 | userid | pmnew | pmtotal |
 ++---+-+
 |  1 | 0 |   0 |
 |  2 | 0 |   0 |
 ++---+-+
 2 rows in set (0.00 sec)
 
 mysql select * from m;
 +--+---+
 | toid | rd|
 +--+---+
 |1 | 0 |
 |1 | 0 |
 |1 | 0 |
 |1 |12 |
 |1 |15 |
 |1 |   123 |
 |1 | 12312 |
 |1 | 12312 |
 |1 |   123 |
 |2 | 0 |
 |2 | 0 |
 |2 | 1 |
 |2 | 2 |
 +--+---+
 13 rows in set (0.00 sec)
 
 mysql select userid,pmtotal,pmnew, (select count(rd) from m where
 toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
 toid=p.userid) calc_new from p where userid in (select distinct toid from m);
 ++-+---++--+
 | userid | pmtotal | pmnew | calc_total | calc_new |
 ++-+---++--+
 |  1 |   0 | 0 |  9 |3 |
 |  2 |   0 | 0 |   NULL | NULL |
 ++-+---++--+
 Now, the first row has what I want and expect, in calc_total and calc_new...
 but the second row doesn't. Why? Shouldn't the subselects in the field
 selector part (not the where part) be re-executed for each value in the
 IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the
 second row. For example, if I manually fudge the WHERE ... IN, I get:
 
 mysql select userid,pmtotal,pmnew, (select count(rd) from m where
 toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
 toid=p.userid) calc_new from p where userid in (2);
 ++-+---++--+
 | userid | pmtotal | pmnew | calc_total | calc_new |
 ++-+---++--+
 |  2 |   0 | 0 |  4 |2 |
 ++-+---++--+
 
 which is exactly what I want, but all at once :-)
 
 Ideas? Misunderstanding on my part? Bug?
 
 (By the way, what I eventually want to do is an update to set pmtotal and
 pmnew to be the calc_total and calc_new; in the real schema this is a
 simplified version of, they are essentially 'caches' of the new/total
 counts...)

Thanks for the report. I added your report to the MySQL bug database:
http://bugs.mysql.com/bug.php?id=860


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

subselect question... shouldn't this work?

[Ben Margolin]

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, here's the tables in question:

mysql describe m;
+---+-+---+--+-+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
+---+-+---+--+-+-+---+
| toid  | int(11) | binary| YES  | | NULL|   |
| rd| int(11) | binary| YES  | | NULL|   |
+---+-+---+--+-+-+---+

mysql describe p;
+-+-+---+--+-+-+---+
| Field   | Type| Collation | Null | Key | Default | Extra |
+-+-+---+--+-+-+---+
| userid  | int(11) | binary|  | PRI | 0   |   |
| pmnew   | int(11) | binary| YES  | | NULL|   |
| pmtotal | int(11) | binary| YES  | | NULL|   |
+-+-+---+--+-+-+---+

and the data in the tables...

mysql select * from p;
++---+-+
| userid | pmnew | pmtotal |
++---+-+
|  1 | 0 |   0 |
|  2 | 0 |   0 |
++---+-+
2 rows in set (0.00 sec)

mysql select * from m;
+--+---+
| toid | rd|
+--+---+
|1 | 0 |
|1 | 0 |
|1 | 0 |
|1 |12 |
|1 |15 |
|1 |   123 |
|1 | 12312 |
|1 | 12312 |
|1 |   123 |
|2 | 0 |
|2 | 0 |
|2 | 1 |
|2 | 2 |
+--+---+
13 rows in set (0.00 sec)

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (select distinct toid from m);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  1 |   0 | 0 |  9 |3 |
|  2 |   0 | 0 |   NULL | NULL |
++-+---++--+
Now, the first row has what I want and expect, in calc_total and calc_new...
but the second row doesn't. Why? Shouldn't the subselects in the field
selector part (not the where part) be re-executed for each value in the
IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the
second row. For example, if I manually fudge the WHERE ... IN, I get:

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (2);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  2 |   0 | 0 |  4 |2 |
++-+---++--+

which is exactly what I want, but all at once :-)

Ideas? Misunderstanding on my part? Bug?

(By the way, what I eventually want to do is an update to set pmtotal and
pmnew to be the calc_total and calc_new; in the real schema this is a
simplified version of, they are essentially 'caches' of the new/total
counts...)

Any comments appreciated.

Ben Margolin


=
[ Ben Margolin -- [EMAIL PROTECTED] -- [EMAIL PROTECTED]  ]

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[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 theory and does not help you much :) Thus you can solve
this without subselects:

SELECT userid, pmtotal, pmnew, 
   count(*) calc_total, 
   sum(if(rd=0,1,0) calc_new
  FROM m,
   p
 WHERE userid = toid
 GROUP BY userid

OR ( if you insist on derived tables and subselects )

SELECT userid, pmtotal, pmnew, 
   m2.calc_total calc_total, 
   m1.calc_new calc_new
  FROM m,
   p,
   ( SELECT toid, count(*) calc_new
   FROM m
  WHERE rd = 0
  GROUP BY toid ) m1,
   ( SELECT toid, count(*) calc_total
   FROM m
  GROUP BY toid ) m2
 WHERE userid IN ( SELECT distinct toid
 FROM m )
   AND p.userid = m2.toid
   AND p.userid = m1.toid

But I would use the first one...

/rudy

-Original Message-
From: Ben Margolin [mailto:[EMAIL PROTECTED] 
Sent: woensdag 16 juli 2003 1:36
To: [EMAIL PROTECTED]
Subject: subselect question... shouldn't this work?


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, here's the tables in question:

mysql describe m;
+---+-+---+--+-+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
+---+-+---+--+-+-+---+
| toid  | int(11) | binary| YES  | | NULL|   |
| rd| int(11) | binary| YES  | | NULL|   |
+---+-+---+--+-+-+---+

mysql describe p;
+-+-+---+--+-+-+---+
| Field   | Type| Collation | Null | Key | Default | Extra |
+-+-+---+--+-+-+---+
| userid  | int(11) | binary|  | PRI | 0   |   |
| pmnew   | int(11) | binary| YES  | | NULL|   |
| pmtotal | int(11) | binary| YES  | | NULL|   |
+-+-+---+--+-+-+---+

and the data in the tables...

mysql select * from p;
++---+-+
| userid | pmnew | pmtotal |
++---+-+
|  1 | 0 |   0 |
|  2 | 0 |   0 |
++---+-+
2 rows in set (0.00 sec)

mysql select * from m;
+--+---+
| toid | rd|
+--+---+
|1 | 0 |
|1 | 0 |
|1 | 0 |
|1 |12 |
|1 |15 |
|1 |   123 |
|1 | 12312 |
|1 | 12312 |
|1 |   123 |
|2 | 0 |
|2 | 0 |
|2 | 1 |
|2 | 2 |
+--+---+
13 rows in set (0.00 sec)

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (select distinct toid
from m);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  1 |   0 | 0 |  9 |3 |
|  2 |   0 | 0 |   NULL | NULL |
++-+---++--+
Now, the first row has what I want and expect, in calc_total and
calc_new...
but the second row doesn't. Why? Shouldn't the subselects in the field
selector part (not the where part) be re-executed for each value in
the
IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for
the
second row. For example, if I manually fudge the WHERE ... IN, I get:

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (2);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  2 |   0 | 0 |  4 |2 |
++-+---++--+

which is exactly what I want, but all at once :-)

Ideas? Misunderstanding on my part? Bug?

(By the way, what I eventually want to do is an update to set pmtotal
and
pmnew to be the calc_total and calc_new; in the real schema this is a
simplified version of, they are essentially 'caches' of the new/total
counts...)

Any comments appreciated.

Ben Margolin


=
[ Ben Margolin -- [EMAIL PROTECTED] -- [EMAIL PROTECTED]  ]

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

-- 
MySQL General Mailing List
For list archives: http

Subselect functionality

[Nils Valentin]

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 TABLE tblname_new SELECT * FROM tblname_ori;

then it works. Or do I have a misunderstanding of subselects (nested queries) 
??

Best regards
-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect functionality

[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. For xample if I 
 try this:
 
 CREATE TABLE tblname_new SELECT * FROM tblname_ori;
 
 then it works. Or do I have a misunderstanding of subselects (nested queries) 
 ??

Nested queries is not only subselects. Yes, you are right, some nested queries like 
INSERT .. SELECT, CREATE .. SELECT was implemented in earlier than 4.1



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect functionality

[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 - from the manual I misunderstood that 
nested queries and subselects are the same, which is obviously not the case 
as I heard now.

I understood that Subselects originally exist of 2 or more SELECT... 
statements.

Best regards

Nils Valentin
Tokyo/Japan




2003 6 25  19:53Egor 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. For xample
  if I try this:
 
  CREATE TABLE tblname_new SELECT * FROM tblname_ori;
 
  then it works. Or do I have a misunderstanding of subselects (nested
  queries) ??

 Nested queries is not only subselects. Yes, you are right, some nested
 queries like INSERT .. SELECT, CREATE .. SELECT was implemented in earlier
 than 4.1



 --
 For technical support contracts, goto https://order.mysql.com/?ref=ensita
 This email is sponsored by Ensita.net http://www.ensita.net/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Egor Egorov
  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
___/   www.mysql.com

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Subselect functionality

[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


 -Original Message-
 From: Nils Valentin [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, June 25, 2003 1:12 AM
 To: [EMAIL PROTECTED]
 Subject: Subselect functionality
 
 
 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 TABLE tblname_new SELECT * FROM tblname_ori;
 
 then it works. Or do I have a misunderstanding of subselects 
 (nested queries) 
 ??
 
 Best regards
 -- 
 ---
 Valentin Nils
 Internet Technology
 
  E-Mail: [EMAIL PROTECTED]
  URL: http://www.knowd.co.jp
  Personal URL: http://www.knowd.co.jp/staff/nils
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql? [EMAIL PROTECTED]
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect functionality

[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 whole context.

So to sum it all up once more nicely and clean (for the records):

Nested queries:

CREATE... SELECT...;
INSERT... SELECT ...;
REPLACE ... SELECT...;

Subelects:

SELECT ... SELECT...;

I think that makes things clearer for me and hopefully for anybody still 
unsure about subselects and nested queries. 

Best regards

Nils Valentin
Tokyo/Japan




2003 6 25  23:19Mike 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

  -Original Message-
  From: Nils Valentin [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, June 25, 2003 1:12 AM
  To: [EMAIL PROTECTED]
  Subject: Subselect functionality
 
 
  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 TABLE tblname_new SELECT * FROM tblname_ori;
 
  then it works. Or do I have a misunderstanding of subselects
  (nested queries)
  ??
 
  Best regards
  --
  ---
  Valentin Nils
  Internet Technology
 
   E-Mail: [EMAIL PROTECTED]
   URL: http://www.knowd.co.jp
   Personal URL: http://www.knowd.co.jp/staff/nils
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql? [EMAIL PROTECTED]

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

need help with subselect workaround

[Sheryl Canter]

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 support subselects. I have a Web site 
that displays a list of programs for sale (shareware). The list displays the authors' 
names. In some cases, more than one author works on a program. In this case, I want to 
display the name of the lead author. I define this programmatically as the author who 
earns the highest royalty rate. I have tables like this:

royalties table (primary key is a combination of AuthorID and UtilityID):

AuthorIDUtilityIDRoyalty
--
Author1  Utility1  0.15
Author2  Utility1  0.10
Author3  Utility2  0.25
Author4  Utility3  0.05
Author5  Utility3  0.20


authors table:

AuthorIDFirstNameLastName

Author1  Joe   Smith
Author2  BrianJones
Author3  Jeff  Tucker
Author4  MichaelMoore
Author5  MarkMann


utilities table:

UtilityIDProgramName

Utility1  ProgName1
Utility2  ProgName2
Utility3  ProgName3


This is my SQL code:

DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties 
  SELECT AuthorID, UtilityID, Royalty, max(Royalty) as MaxRoyalty
  FROM royalties
  WHERE Royalty = MaxRoyalty
  GROUP BY UtilityID;

Unfortunately, the above produces a table with nothing in it. If I take out the WHERE 
clause, I get one line per group (per utility), but the author isn't necessarily the 
one with the highest royalty rate. It seems like the first author in the list is being 
selected.

The SELECT statement for using the above table (once it's properly populated) is:


SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version, u.ShortDescription, 
u.MinorReleaseDate, 
  a.LastName, pr.UtilityID, pr.AuthorID
  FROM utilities u, authors a, ProgramRoyalties pr
   $WhereClause
 u.UtilityID = pr.UtilityID AND a.AuthorID = pr.AuthorID;

How do I get the correct data into the temporary table?? Using the above sample data, 
I'd want it to look like this:

ProgramRoyalties table (primary key is a combination of AuthorID and UtilityID):

AuthorIDUtilityIDRoyalty
--
Author1  Utility1  0.15
Author3  Utility2  0.25
Author5  Utility3  0.20

Thanks in advance for your help.

Sheryl Canter
Permutations Software
www.permutations.com

Re: need help with subselect workaround

[Sheryl Canter]

I got it to work, if anyone's interested (see message below). Here is the
SQL:

DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties
  SELECT UtilityID, max(Royalty) as MaxRoyalty
  FROM royalties
  GROUP BY UtilityID;
SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version,
u.ShortDescription, u.MinorReleaseDate,
  a.LastName, r.UtilityID, r.AuthorID, r.Royalty, pr.UtilityID,
pr.MaxRoyalty
  FROM utilities u, authors a, royalties r, ProgramRoyalties pr
  WHERE
 u.UtilityID = r.UtilityID AND
 r.UtilityID = pr.UtilityID AND
 a.AuthorID = r.AuthorID AND
 r.Royalty = pr.MaxRoyalty;

What I didn't understand was that the temporary table is simply a look-up
table for the max royalty for each utility. I use this in the WHERE clause
of the SELECT statement.

Sheryl Canter
Permutations Software
www.permutations.com


- 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 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 support subselects. I have
a Web site that displays a list of programs for sale (shareware). The list
displays the authors' names. In some cases, more than one author works on a
program. In this case, I want to display the name of the lead author. I
define this programmatically as the author who earns the highest royalty
rate. I have tables like this:

royalties table (primary key is a combination of AuthorID and UtilityID):

AuthorIDUtilityIDRoyalty
--
Author1  Utility1  0.15
Author2  Utility1  0.10
Author3  Utility2  0.25
Author4  Utility3  0.05
Author5  Utility3  0.20


authors table:

AuthorIDFirstNameLastName

Author1  Joe   Smith
Author2  BrianJones
Author3  Jeff  Tucker
Author4  MichaelMoore
Author5  MarkMann


utilities table:

UtilityIDProgramName

Utility1  ProgName1
Utility2  ProgName2
Utility3  ProgName3


This is my SQL code:

DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties
  SELECT AuthorID, UtilityID, Royalty, max(Royalty) as MaxRoyalty
  FROM royalties
  WHERE Royalty = MaxRoyalty
  GROUP BY UtilityID;

Unfortunately, the above produces a table with nothing in it. If I take out
the WHERE clause, I get one line per group (per utility), but the author
isn't necessarily the one with the highest royalty rate. It seems like the
first author in the list is being selected.

The SELECT statement for using the above table (once it's properly
populated) is:


SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version,
u.ShortDescription, u.MinorReleaseDate,
  a.LastName, pr.UtilityID, pr.AuthorID
  FROM utilities u, authors a, ProgramRoyalties pr
   $WhereClause
 u.UtilityID = pr.UtilityID AND a.AuthorID = pr.AuthorID;

How do I get the correct data into the temporary table?? Using the above
sample data, I'd want it to look like this:

ProgramRoyalties table (primary key is a combination of AuthorID and
UtilityID):

AuthorIDUtilityIDRoyalty
--
Author1  Utility1  0.15
Author3  Utility2  0.25
Author5  Utility3  0.20

Thanks in advance for your help.

Sheryl Canter
Permutations Software
www.permutations.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: subselect

[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 like
'%sales%' or designation like '%marketing%')
AND can_ID in

(Select can_ID from TABLE1 where (designation like
'%telecom%' or designation like '%software%'))
if no! any solutions please...
its urgent


--
Paul DuBois
http://www.kitebird.com/
sql, query
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

subselect

[geeta varu]

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 '%telecom%' or
pres_org_curr_desig like '%software%')) 

if no! any solutions please...
its urgent

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

subselect

[geeta varu]

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%')) 

if no! any solutions please...
its urgent

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Re: subselect

[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 PM
 Subject: 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/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

subselect

[geeta varu]

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/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: subselect

[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 support...


 __
 Do you Yahoo!?
 Yahoo! Tax Center - forms, calculators, tips, more
 http://taxes.yahoo.com/

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re:count in subselect problem

[Nasser Ossareh]

Well it all depends which version of mysql you are using... versions prior to 4 don't 
support sub-select..



-- Stijn Van Rompaey [EMAIL PROTECTED] writes:

Return-Path: [EMAIL PROTECTED]
Received: from mx11.nyc.untd.com (mx11.nyc.untd.com [10.140.24.71])
by maildeliver03.nyc.untd.com with SMTP id AAA9DDMXAABM9SBJ
for [EMAIL PROTECTED] (sender 
[EMAIL PROTECTED]);
Fri, 24 Jan 2003 16:22:40 -0500 (EST)
Received: from web.mysql.com (web.mysql.com [213.136.49.183])
by mx11.nyc.untd.com with SMTP id AAA9DDMW9AUWDGJA
for [EMAIL PROTECTED] (sender 
[EMAIL PROTECTED]);
Fri, 24 Jan 2003 16:22:39 -0500 (EST)
Received: (qmail 27937 invoked by uid 7797); 24 Jan 2003 15:18:00 -
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org)
List-ID: mysql.mysql.com
Precedence: bulk
List-Help: mailto:[EMAIL PROTECTED]
List-Unsubscribe: mailto:[EMAIL PROTECTED]
List-Post: mailto:[EMAIL PROTECTED]
List-Subscribe: mailto:[EMAIL PROTECTED]
Delivered-To: mailing list [EMAIL PROTECTED]
Received: (qmail 27923 invoked from network); 24 Jan 2003 15:18:00 -
Message-ID: 00b601c2c3bb$cae633a0$c031e0d5@testrk98
From: Stijn Van 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 6.00.2600.
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.


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
from forum F, topic T where F.id=T.forum_id AND T.reply_id=0
GROUP BY F.id




2nd problem:

SELECT T.title,
T.id,
P.nick,
(select count(*) from topic where reply_id = T.reply_id)
replies
 FROM topic T, profile P WHERE T.author_id=P.id AND T.forum_id=1 and
reply_id=0;



what is wrong with these queries?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

count in subselect problem

[Stijn Van Rompaey]

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
from forum F, topic T where F.id=T.forum_id AND T.reply_id=0
GROUP BY F.id




2nd problem:

SELECT T.title,
T.id,
P.nick,
(select count(*) from topic where reply_id = T.reply_id)
replies
 FROM topic T, profile P WHERE T.author_id=P.id AND T.forum_id=1 and
reply_id=0;



what is wrong with these queries?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: solution for opposite of this join / join from this subselect

[M Wells]

 exactly the same recordset as the RIGHT JOIN example above it.
So, the 'direction' of the join indicates which side of the tables
listed in the FROM clause will return all of their rows, and which side
will only return the data matched by the ON clause (and which can then
usually be further qualified / restrained by WHERE clauses).

In theory, it should be possible to retrieve a recordset that combines
the LEFT JOIN and RIGHT JOIN results from our examples above in a single
recordset. 

This would probably look something like:

NULL,NULL,project2
employee1,employee details1,project1
employee2,employee details2,project3
employee3,employee details3,NULL
employee4,employee details4,project4

This is usually performed by a FULL JOIN, but I am under the impression
that MySQL has yet to implement FULL JOINs (certainly, I can't get them
to work on ver 3.23.53 and the documentation for my version indicates
FULL JOINs are expected in ver 4.0 or 4.1).

Now, to go back to your original question, the people / events /
attendance example employed both a LEFT JOIN and a RIGHT JOIN because
the attendance table was acting as an intermediary between the people
and events tables. From my Microsoft Acess background, I would call this
a 'FIND UNMATCHED' query, although instead of finding the records in one
table that aren't reflected in another [1], we're finding and combining
the records from two tables that aren't associated with each other in a
third. I personally can't think of a way of representing the same query
you have developed but only using LEFT JOINs. I'm not in a position to
say it can't be done, but I certainly can't think of a way to do it.

All the best,

M Wells

[1] As a simple example of a 'find unmatched' query, if we had a table
that contained a record for each of the files on your computer and a
table of all of the files from your computer that you've backed up onto
a CD, then finding the records in your [allfiles] table that are
unmatched (i.e. don't exist) in your [backeduptocd] table should equate
to a list of all of the files on your computer that have yet to be
backed up to CD

-Original Message-
From: Josh L Bernardini [mailto:[EMAIL PROTECTED]] 
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 returns a list of people not attending a particular event,
given
an events table, a people table, and a many-many epeople table between
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of
what
you might need in most many - many relationships.

But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to
work?
could it be rewritten to use a left join?

mysql SELECT lastname, firstname, title, event
- FROM people p
- LEFT JOIN epeople ep on p.id = ep.pid
- right join events e ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;


I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join  - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;


now whats the functional difference between this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;

and this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;

as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?

thanks for any insights,
jb



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL

Re: solution for opposite of this join / join from this subselect

[Bill Easton]

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 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
 them. You can modify the where clause to show all the people attending a
 particular event, all the events a person isn't/is attending - most of
what
 you might need in most many - many relationships.

 But I still haven't figured out the importance of left joins vs. right
 joins. can anyone explain why this statement requires a right join to
work?
 could it be rewritten to use a left join?

 mysql SELECT lastname, firstname, title, event
 - FROM people p
 - LEFT JOIN epeople ep on p.id = ep.pid
 - right join events e ON e.id = ep.eid
 - WHERE ep.pid IS NULL
 - and ep.eid is null
 - and e.id=2
 - ORDER BY e.id;

It can.  How about:

mysql SELECT lastname, firstname, title, event
- FROM events e
-   LEFT JOIN (people p
-  LEFT JOIN epeople ep on p.id = ep.pid)
-   ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;

This appears to work in MySQL 4.0.9; Mysql 3.23 didn't allow the
parentheses.
In fact, 4.0.9 appears to work without the parentheses, but 3.23 still
doesn't.
The point is, you want all of the people and all of the events, so you have
to have people on the left side of a left join and events on the left side
of a left join.

[...]

 now whats the functional difference between this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and e.id = 2
 ORDER BY ep.eid;

 and this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and ep.eid=2
 ORDER BY e.id;

 as written the difference is in the and statements but in my result set
 ep.eid == e.id == 2 so why can't you use the second statement
 interchangably with the first?

No, it's not so that ep.eid == e.id.
In the result of the join, before applying the where clause, ep.eid is null
and e.id is not null for a row corresponding to an event which has no
associated people.

 thanks for any insights,
 jb






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

solution for opposite of this join / join from this subselect

[Josh L Bernardini]

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
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of what
you might need in most many - many relationships.

But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to work?
could it be rewritten to use a left join?

mysql SELECT lastname, firstname, title, event
- FROM people p
- LEFT JOIN epeople ep on p.id = ep.pid
- right join events e ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;


I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join  - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;


now whats the functional difference between this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;

and this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;

as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?

thanks for any insights,
jb



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: join from this subselect

[Bill Easton]

How about:

  select people.id ...
  from people left join epeople
on  epeople.pid=people.id
and epeople.eid=2
  where epeople.pid is null;

The left join gives you:
  (1)  rows for people who attended event 2, with epeople columns populated
  (2)  rows for people who did not attend event 2, with nulls in epeople
columns
Then the where restricts to (2).

More generally,
  select ... from L left join R on CONDITION
gives you:
  (1)  the result of the inner join,
 select ... from L, R where CONDITION
  (2)  the rows from L that were not used in (1), with nulls for the R
columns
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 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 epeople.pid=people.id
 where epeople.eid=2);

 Thought it would be:
[...]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

join from this subselect

[Josh L Bernardini]

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 epeople.pid=people.id
where epeople.eid=2);

Thought it would be:

select epeople.pid, concat(lastname, , , firstname) as name from epeople
  left join people on epeople.pid=people.id
where epeople.eid=2
  and people.id is null;

but I get an empty set.

The following lists people attending an event:

select epeople.pid, concat(lastname, , , firstname) as name from epeople
  left join people on epeople.pid=people.id
where epeople.eid=2;

tia,
jb

PS is 4.1 available anywhere so I could just use the subselect. Under 4.1,
are subselects or joins more efficient?



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: join from this subselect

[Adolfo Bello]

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 not in
 (select epeople.pid from
 epeople
   left join people on epeople.pid=people.id
 where epeople.eid=2);
 
 Thought it would be:
 
 select epeople.pid, concat(lastname, , , firstname) as name from epeople
   left join people on epeople.pid=people.id
 where epeople.eid=2
   and people.id is null;
 
 but I get an empty set.
In this query you're querying events with no attendants (people.id IS
NULL) and with event Id 2. Did event 2 have at least an attendant? If
so, then the result must be empty.

I guess you will need a TEMP table.

HTH

-- 
__   
   / \\   @   __ __@   Adolfo Bello [EMAIL PROTECTED]
  /  //  // /\   / \\   // \  //   Bello Ingenieria S.A, ICQ: 65910258
 /  \\  // / \\ /  //  //  / //cel: +58 416 609-6213
/___// // / _/ \__\\ //__/ // fax: +58 212 952-6797
www.bisapi.com   //pager: www.tun-tun.com (609-6213)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Question -Subselect

[Terence Ng]

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, 
 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
 
 
 SELECT
 lcopen.bank, 
 SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
 open,
 #
 SUM(lcreceive.amount_us) where lcreceive.due_date 
 current_date,
 #
 # the above statement is not correct 
 # 
 FROM lcopen, lcreceive 
 AND lcopen.id=lcreceive.id 
 GROUP BY lcopen.bank;
 
 
 
  
  
  
 Terence Ng
 
 

___
 Do You Yahoo!?
 Get your free @yahoo.com.hk address at
 http://mail.english.yahoo.com.hk
 

-
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list
 archive)
 
 To request this thread, e-mail
 [EMAIL PROTECTED]
 To unsubscribe, e-mail

[EMAIL PROTECTED]
 Trouble unsubscribing? Try:
 http://lists.mysql.com/php/unsubscribe.php
 
 
   
 
 
 
 

-
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list
 archive)
 
 To request this thread, e-mail
 [EMAIL PROTECTED]
 To unsubscribe, e-mail

[EMAIL PROTECTED]
 Trouble unsubscribing? Try:
 http://lists.mysql.com/php/unsubscribe.php
  

___
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Question -Subselect

[gerald_clark]

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 : SUM(lcreceive.amount_us)
#and NOT :
#SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open


SELECT
lcopen.bank, 
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date 
current_date,
#
# the above statement is not correct 
# 
FROM lcopen, lcreceive 
AND lcopen.id=lcreceive.id 
GROUP BY lcopen.bank;






Terence Ng


___
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


 




-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

subselect workaround help?

[Lefevre, Steven]

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 Student belongs to one class.


So, I have the StudentID. I want to get the Classes.Name...
Final result should look like

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 )

I know that probably won't work as a subselect query, but that's my starting
point.

Any help? Thanks!

Steve


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: subselect workaround help?

[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 columns:

 Students
  - Name
  - StudentID (PK)
  - ClassID

 Classes
  - ClassID (PK)
  - Name

  Each Student belongs to one class.


 So, I have the StudentID. I want to get the Classes.Name...
 Final result should look like

 Student | Class
 --
 Steve Lefevre | Math101
 Stacy Adams | Intro to SQL


Hi Steve, try

SELECT `Students`.`Name` AS Student, `Classes`.`Name` AS Class FROM
`Students` LEFT JOIN `Classes` USING `ClassID`;

Regards,

Alexander




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: subselect workaround help?

[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 you add them to StudentClasses table. In
your previous layout (which was not normalized completely) you are repeating
Name/StudentID each time a student is put into a class (redundant data is a
no-no).

Now to get all classes you just:

SELECT C.* FROM Classes AS C, StudentsClasses AS R WHERE
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-

'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 Student belongs to one class.


So, I have the StudentID. I want to get the Classes.Name...
Final result should look like

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 )

I know that probably won't work as a subselect query, but that's my starting
point.

Any help? Thanks!

Steve


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: subselect workaround help?

[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
 --
 Steve Lefevre | Math101
 Stacy Adams | Intro to SQL

How about:

  SELECT Students.Name as Student, Classes.Name as Class
FROM Students, Classes
   WHERE Students.ClassID = Classes.ClassID



On a side note: Some of your schema naming will eventually cause you trouble, or at 
least win you some grief from other programmers.

- A common idiom is to name a table for what ONE record of data will
  represent. That means that you'd name the table that holds student
  information as 'student' instead of 'Students' since one record only
  contains one student.


- It's a bit redundant to have to say 'Students.StudentID'. It might make
  more sence to readers to see 'student.id' and 'student.class_id'.


- You'll notice that I've been using all lower case names. MySQL will
  allow loose case of column names, but not table names. So, you can say:
SELECT name FROM Students;
  But not:
SELECT Name FROM students;

  If you always use lower case, then you and your co-workers will always know
  how to type in names without having to get out a copy of the schema first.


OK, I'll stop preaching now. Let us know if you need more help with this.


---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/

sql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: subselect workaround help?

[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.ClassID )

Try 
SELECT Student.Name, Classes.Name FROM Students, Classes WHERE
Students.ClassID = Classes.ClassID.

If you only wnat it for one syudent do:
SELECT Student.Name, Classes.Name FROM Students, Classes WHERE
Students.ClassID = Classes.ClassID AND Students.StudentID = 'some
ID'

Hope this helps.


Scott Pippin
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Question -Subselect

[Terence Ng]

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


SELECT
lcopen.bank, 
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date 
current_date,
#
# the above statement is not correct 
# 
FROM lcopen, lcreceive 
AND lcopen.id=lcreceive.id 
GROUP BY lcopen.bank;



 
 
 
Terence Ng


___
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Question - subselect

[Terence Ng]

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


SELECT
lcopen.bank, 
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date 
current_date,
#
# the above statement is not correct 
# 
FROM lcopen, lcreceive 
AND lcopen.id=lcreceive.id 
GROUP BY lcopen.bank;



 
 
 
Terence Ng


___
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php