Re: Complex Select Query

[Ashish Mukherjee]

Alternatively, you could just code a recursive function to do the same
instead of doing it in SQL or stored procs.

On Tue, Aug 24, 2010 at 11:01 AM, Victor Subervi wrote:

> On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley
> wrote:
>
> > >What I'm trying to accomplish is to order the results such that after
> > >stacking the data for all results for a certain category, that the next
> > >results to be stacked should be those whose parent = the former
> category,
> > >then move on to the next category, etc. How do I do this?
> >
> > It's a tree. See
> > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and
> > http://www.artfulsoftware.com/infotree/treequeryperformance.pdf.
> >
> >
> How do I cut down the tree? That stuff is *way* too complex for my needs. I
> just thought I'd make my presentation of data a little cleaner but frankly
> it ain't worth going through all that learning and experimentation to do
> it.
> If there isn't another way I'll just forget about it. Any other suggestions
> would be nice.
> TIA.
> Victor
>
> > -
> >
> > -Original Message-
> > >From: Victor Subervi 
> > >Sent: Aug 24, 2010 1:14 PM
> > >To: mysql@lists.mysql.com
> > >Subject: Complex Select Query
> > >
> > >Hi;
> > >I have the following query:
> > >
> > >select * from spreadsheets s join products p on p.Item=s.Item join
> > >categories c on p.Category=c.ID where s.Client=%s order by p.category,
> > >c.parent;
> > >
> > >mysql> describe products;
> > >+-+--+--+-+-++
> > >| Field   | Type | Null | Key | Default | Extra  |
> > >+-+--+--+-+-++
> > >| ID  | int(4)   | NO   | PRI | NULL| auto_increment |
> > >| Category| int(3)   | YES  | | NULL||
> > >| Item| varchar(20)  | YES  | UNI | NULL||
> > >| Description | varchar(255) | YES  | | NULL||
> > >| UOM | varchar(20)  | YES  | | NULL||
> > >| Cost| float(7,2)   | YES  | | NULL||
> > >+-+--+--+-+-++
> > >6 rows in set (0.00 sec)
> > >
> > >mysql> describe categories;
> > >+--+-+--+-+-++
> > >| Field| Type| Null | Key | Default | Extra  |
> > >+--+-+--+-+-++
> > >| ID   | int(3)  | NO   | PRI | NULL| auto_increment |
> > >| Category | varchar(20) | YES  | UNI | NULL||
> > >| Parent   | varchar(20) | YES  | | NULL||
> > >+--+-+--+-+-++
> > >
> > >What I'm trying to accomplish is to order the results such that after
> > >stacking the data for all results for a certain category, that the next
> > >results to be stacked should be those whose parent = the former
> category,
> > >then move on to the next category, etc. How do I do this?
> > >TIA,
> > >Victor
> >
> >
>

Re: Complex Select Query

[Victor Subervi]

On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley
wrote:

> >What I'm trying to accomplish is to order the results such that after
> >stacking the data for all results for a certain category, that the next
> >results to be stacked should be those whose parent = the former category,
> >then move on to the next category, etc. How do I do this?
>
> It's a tree. See
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and
> http://www.artfulsoftware.com/infotree/treequeryperformance.pdf.
>
>
How do I cut down the tree? That stuff is *way* too complex for my needs. I
just thought I'd make my presentation of data a little cleaner but frankly
it ain't worth going through all that learning and experimentation to do it.
If there isn't another way I'll just forget about it. Any other suggestions
would be nice.
TIA.
Victor

> -
>
> -Original Message-
> >From: Victor Subervi 
> >Sent: Aug 24, 2010 1:14 PM
> >To: mysql@lists.mysql.com
> >Subject: Complex Select Query
> >
> >Hi;
> >I have the following query:
> >
> >select * from spreadsheets s join products p on p.Item=s.Item join
> >categories c on p.Category=c.ID where s.Client=%s order by p.category,
> >c.parent;
> >
> >mysql> describe products;
> >+-+--+--+-+-++
> >| Field   | Type | Null | Key | Default | Extra  |
> >+-+--+--+-+-++
> >| ID  | int(4)   | NO   | PRI | NULL| auto_increment |
> >| Category| int(3)   | YES  | | NULL||
> >| Item| varchar(20)  | YES  | UNI | NULL||
> >| Description | varchar(255) | YES  | | NULL||
> >| UOM | varchar(20)  | YES  | | NULL||
> >| Cost| float(7,2)   | YES  | | NULL||
> >+-+--+--+-+-++
> >6 rows in set (0.00 sec)
> >
> >mysql> describe categories;
> >+--+-+--+-+-++
> >| Field| Type| Null | Key | Default | Extra  |
> >+--+-+--+-+-++
> >| ID   | int(3)  | NO   | PRI | NULL| auto_increment |
> >| Category | varchar(20) | YES  | UNI | NULL||
> >| Parent   | varchar(20) | YES  | | NULL||
> >+--+-+--+-+-++
> >
> >What I'm trying to accomplish is to order the results such that after
> >stacking the data for all results for a certain category, that the next
> >results to be stacked should be those whose parent = the former category,
> >then move on to the next category, etc. How do I do this?
> >TIA,
> >Victor
>
>

Re: Complex Select Query

[Peter Brawley]

>What I'm trying to accomplish is to order the results such that after
>stacking the data for all results for a certain category, that the next
>results to be stacked should be those whose parent = the former category,
>then move on to the next category, etc. How do I do this?

It's a tree. See 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and 
http://www.artfulsoftware.com/infotree/treequeryperformance.pdf.

PB

-

-Original Message-
>From: Victor Subervi 
>Sent: Aug 24, 2010 1:14 PM
>To: mysql@lists.mysql.com
>Subject: Complex Select Query
>
>Hi;
>I have the following query:
>
>select * from spreadsheets s join products p on p.Item=s.Item join
>categories c on p.Category=c.ID where s.Client=%s order by p.category,
>c.parent;
>
>mysql> describe products;
>+-+--+--+-+-++
>| Field   | Type | Null | Key | Default | Extra  |
>+-+--+--+-+-++
>| ID  | int(4)   | NO   | PRI | NULL| auto_increment |
>| Category| int(3)   | YES  | | NULL||
>| Item| varchar(20)  | YES  | UNI | NULL||
>| Description | varchar(255) | YES  | | NULL||
>| UOM | varchar(20)  | YES  | | NULL||
>| Cost| float(7,2)   | YES  | | NULL||
>+-+--+--+-+-++
>6 rows in set (0.00 sec)
>
>mysql> describe categories;
>+--+-+--+-+-++
>| Field| Type| Null | Key | Default | Extra  |
>+--+-+--+-+-++
>| ID   | int(3)  | NO   | PRI | NULL| auto_increment |
>| Category | varchar(20) | YES  | UNI | NULL||
>| Parent   | varchar(20) | YES  | | NULL||
>+--+-+--+-+-++
>
>What I'm trying to accomplish is to order the results such that after
>stacking the data for all results for a certain category, that the next
>results to be stacked should be those whose parent = the former category,
>then move on to the next category, etc. How do I do this?
>TIA,
>Victor


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Complex SELECT

[Andre Matos]

It worked like a charm.

Thanks for the help!

Andre


On 22-Sep-08, at 2:58 PM, Jim Lyons wrote:

look at using the "group_concat" function on OptionalDesc and group  
by the other fields.


On Mon, Sep 22, 2008 at 1:40 PM, Andre Matos <[EMAIL PROTECTED] 
> wrote:

Hello,

I need help with a SQL Select statement:

I have three tables: report, category, and optional

a) "report" table has 3 fields:

ReportId, CategoryId, ReportDesc

b) "category" table has 2 fields:

CategoryId, CategoryDesc

c) "optional" table has three fields:

OptionalId, CategoryId, OptionalDesc


I would like to have something like this:

---
| ReportId | CategoryDesc |  OptionalDesc   | ReportDesc|
---
|   0001 | Example 1| late, query, test |  report desc  
abc |
|   0002 | Example 2| query  | test  
123|
|   0003 | Example 3| | test  
xzy |
|   0004 | Example 1| late, test |  report  
desc abc |

---

However, when performing my SELECT using LEFT JOIN, I am getting  
something like this:


---
| ReportId | CategoryDesc |  OptionalDesc   | ReportDesc|
---
|   0001 | Example 1| late  |   
report desc abc |
|   0001 | Example 1| query  |  report  
desc abc |
|   0001 | Example 1| test  |   
report desc abc |
|   0002 | Example 2| query  | test  
123|
|   0003 | Example 3| | test  
xzy |
|   0004 | Example 1| late |  report  
desc abc |
|   0004 | Example 1| test |  report  
desc abc |

---


Is it possible to have the column "OptionalDesc" as showed in the  
first table above?


Thanks for any help.

Andre

--
Andre Matos
[EMAIL PROTECTED]




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




--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com


--
Dr. André Matos
[EMAIL PROTECTED]

Re: Complex SELECT

[Jim Lyons]

look at using the "group_concat" function on OptionalDesc and group by the
other fields.

On Mon, Sep 22, 2008 at 1:40 PM, Andre Matos <[EMAIL PROTECTED]>wrote:

> Hello,
>
> I need help with a SQL Select statement:
>
> I have three tables: report, category, and optional
>
> a) "report" table has 3 fields:
>
> ReportId, CategoryId, ReportDesc
>
> b) "category" table has 2 fields:
>
> CategoryId, CategoryDesc
>
> c) "optional" table has three fields:
>
> OptionalId, CategoryId, OptionalDesc
>
>
> I would like to have something like this:
>
>
> ---
> | ReportId | CategoryDesc |  OptionalDesc   | ReportDesc|
>
> ---
> |   0001 | Example 1| late, query, test |  report desc abc |
> |   0002 | Example 2| query  | test 123
>|
> |   0003 | Example 3| | test xzy
>   |
> |   0004 | Example 1| late, test |  report desc abc
> |
>
> ---
>
> However, when performing my SELECT using LEFT JOIN, I am getting something
> like this:
>
>
> ---
> | ReportId | CategoryDesc |  OptionalDesc   | ReportDesc|
>
> ---
> |   0001 | Example 1| late  |  report desc
> abc |
> |   0001 | Example 1| query  |  report desc abc
> |
> |   0001 | Example 1| test  |  report desc
> abc |
> |   0002 | Example 2| query  | test 123
>|
> |   0003 | Example 3| | test xzy
>   |
> |   0004 | Example 1| late |  report desc
> abc |
> |   0004 | Example 1| test |  report desc
> abc |
>
> ---
>
>
> Is it possible to have the column "OptionalDesc" as showed in the first
> table above?
>
> Thanks for any help.
>
> Andre
>
> --
> Andre Matos
> [EMAIL PROTECTED]
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: Complex select across multiple tables

[SGreen]

Scott Haneda <[EMAIL PROTECTED]> wrote on 11/16/2005 05:44:05 AM:

> Some of these tables are rather large, I would put the schema in there, 
but
> I think it just adds to the mess.
> 
> Tables
> `users` field id is auto inc pk
> `orders` field id is auto inc pk
> `products` field id is auto inc pk
> 
> `order_items` field order_id is the key for hooking up order_items to
> orders, also, in `order_items` I store order_id which comes from the 
users
> table.  I also have product_id which is how the product is related to an
> order item.
> 
> Some of the products can be marked as instock or out of stock, with
> products.ship_status = 1 or 0
> 
> What I need, is a select, that grabs all order records, joined on the
> order_items for that order, where the products.ship_status is = 1.  If 
ANY
> of the products do not have a ship_status of 1, it should not be include 
in
> the results.
> 
> I also need to further limit this based on a date field in the orders 
table,
> which can be a date in the future, or NULL, it is called 
delayed_ship_date.
> So the limit is WHERE delayed_ship_date is within 13 days of NOW() OR 
where
> delayed_ship_date is null.
> 
> Finally, only where the order status is either 'pre-order' or 'delayed'
> 
> To better explain, we have orders in a system, each order can have many
> order items.  However, each of the order items may or may not be in 
stock.
> I only want to see orders that can be fulfilled.  I further want to only 
see
> orders where the delayed_ship_date is within 13 days of today, or where 
it
> is NULL, in which case, it means the user wants the stuff as soon as
> possible.
> 
> I know it is a little strange, we are dealing with perishables, so 
random
> shipping dates do not always apply.  I seem to be able to get this to 
sort
> of work, but I end up getting back thousands of records where I want 
just
> one for the order, not one for each and ever order item.
> 
> -- 
> -
> Scott HanedaTel: 415.898.2602
>  Novato, CA U.S.A.
> 
> 

## First, let's identify which products have a ship_status=1

CREATE TEMPORARY TABLE tmpProducts (KEY(id)) SELECT id
FROM products p
WHERE ship_status=1;

## Now let's make a list of all orders that meet your date criteria and 
shipping status

CREATE TEMPORARY TABLE tmpOrders (KEY(ID)) (
SELECT id
FROM orders
WHERE delayed_ship_date < = NOW() + INTERVAL 13 DAY
AND status IN ('pre-order','delayed')
) UNION (
SELECT id
FROM orders
WHERE delayed_ship_date is NULL
AND status IN ('pre-order','delayed')
);

## Let's combine those tables with order_items to single out only those 
orders that 
## not only meet your order status and date criteria but also have 
products 
## whose ship_status=1.

CREATE TEMPORARY TABLE tmpBackorders (KEY(ID)) SELECT DISTINCT ord.id
FROM tmpOrders ord
INNER JOIN order_items oi
on oi.order_id = ord.id
INNER JOIN tmpProducts tp
on tp.id = oi.product_id;

## now you have a list of all of the backorders that should be shipped 
ASAP or within 13 days
## You can use that list to get all of the information for each order back 
from the 
## `orders` table

SELECT o.*
FROM orders o
inner join tmpBackorders b
on b.id = o.id
order by o.delayed_ship_date;

## Last thing: the database is not your mommy. Clean up before you leave 
the session

DROP TEMPORARY TABLE tmpBackorders, tmpOrders, tmpProducts;



Make sense?  I split it up this way for speed. To do this all in one 
complex statement _IS_ possible (I know I can write a single statement to 
return the same results) but you didn't sound like you had an hour to wait 
on that query to finish. The extra time it would have taken to process the 
single-statement version  has to do with how many JOINs were going to be 
used and how much of your existing data you would wind up throwing out 
anyway. By pre-limiting ourselves to selected subsets of your data, we 
drastically minimize the number of comparisons necessary to compute 
tmpBackorders thus reducing how long it takes to give you the desired 
information from the orders table. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Complex select statement

[Rudy Metzger]

You are not allowed to use grouping functions in the WHERE clause. To restrict on 
grouped values, you have to put them into the HAVING clause.

Cheers
/rudy

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 15 juli 2003 23:25
To: Rudy Metzger; [EMAIL PROTECTED]
Subject: RE: Complex select statement

Still having a problem with this.  Still have one last thing that isn't working.  This 
is MySQL 4.0.13.

UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - 
table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp);

This is giving me: 
ERROR : Invalid use of group function.

I am trying to update the period_count field for all the records in table_tmp, by 
setting the equal to the count from table_tmp MINUS the count from table2 where the 
id's match and it is the newest record in table2.

Any ideas?
Please don't tell me to how to do it 4.1!  Please! :)

Roy

-Original Message-
From: Rudy Metzger [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 14, 2003 3:49 AM
To: Roy Walker; [EMAIL PROTECTED]
Subject: RE: Complex select statement

I never heard before that you can use a select statement in an arithmetic expression. 
Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) 
should work.

Anyway, it is considered a "subselect" and therefore does not work yet. However, in 
4.1 you should also be able to formulate it like this:

INSERT INTO table2( id, count, period_count, date )
SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1) 
  FROM table1 t1,
   (SELECT count
  FROM table2
 WHERE id = t1.id
 ORDER BY timestamp desc
 LIMIT 1) x

This would also be the way how I would do it in a different DB (e.g. ORACLE).

Cheers
/rudy

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 18:30
To: [EMAIL PROTECTED]
Subject: RE: Complex select statement

Eva,

Thanx for your help.  Still having an issue with this.  I forgot to mention I am 
running MySQL 4.0.13.  Tried both of these statements:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - 
(SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
(count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section.  
Is this illegal syntax?  Would this be considered a subselect?

Thanx,
Roy

-Original Message-
From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 12:30 AM
To: Roy Walker
Subject: RE: Complex select statement

try this:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
(count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

i am not sure, the subselect is ok. if the "id1" alias will not work, then
try to use a variable (... SELECT  @id1:=id ... WHERE [EMAIL PROTECTED] ...).
i just corrected a few syntax mistakes in your query. 

eva

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED]
Sent: Friday, July 11, 2003 7:19 AM
To: [EMAIL PROTECTED]
Subject: Complex select statement


If anyone could tell me what I am doing wrong here, I would greatly
appreaciate it.
 
Have the following tables:
 
table1: id, count
table2: id, count, period_count, date, timestamp
 
Trying to do the following; get all rows from table 1 and insert them into
table2 while setting period_count to count.table1 minus the most recent
entry for that id in count.table2, then set the date field to the previous
day.
 
Here goes:
 
INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1,
(count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';

Sorry if this is easy, but I could not find an example of any mathematic
functions while doing an INSERT ... SELECT.
 
Thanx,
Roy

-- 
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: Complex select statement

[Roy Walker]

Alright, I am almost there, got one problem now.  There are multiple
entries for each id in table2.  I need only the records with the newest
timestamp for each id.  Want to do something like this, but doesn't
work:

INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id,
table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM
table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR
'NULL' AND timestamp=MAX(table2.timestamp) GROUP BY table2.id;

This gives me:
ERROR : Invalid use of group function

So I tried something like:

INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id,
table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM
table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR
'NULL' GROUP BY table2.id HAVING MAX(timestamp);

Which will return 0 results every time.

Can't use 'ORDER BY desc limit 1' since that will only give me 1 entry.

Anything else I can try?  There has to be a way to get all the counts
from table2 which have the newest timestamp for each id in the table!

Please help!

Roy

-Original Message-
From: Michael Iatauro [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 15, 2003 4:27 PM
To: [EMAIL PROTECTED]
Subject: Re: Complex select statement

As far as I know, MAX only returns the greatest row, so what you really 
want at the end is
WHERE table_tmp.id=table2.id AND table2.timestamp=MAX(table2.timestamp)

~MJI

Roy Walker wrote:
> Still having a problem with this.  Still have one last thing that
isn't working.  This is MySQL 4.0.13.
> 
> UPDATE table_tmp,table2_daily SET table_tmp.period_count =
table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND
MAX(table2.timestamp);
> 
> This is giving me: 
> ERROR : Invalid use of group function.
> 
> I am trying to update the period_count field for all the records in
table_tmp, by setting the equal to the count from table_tmp MINUS the
count from table2 where the id's match and it is the newest record in
table2.
> 
> Any ideas?
> Please don't tell me to how to do it 4.1!  Please! :)
> 
> Roy
> 


-- 
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: Complex select statement

[Michael Iatauro]

As far as I know, MAX only returns the greatest row, so what you really 
want at the end is
WHERE table_tmp.id=table2.id AND table2.timestamp=MAX(table2.timestamp)

~MJI

Roy Walker wrote:
Still having a problem with this.  Still have one last thing that isn't working.  This is MySQL 4.0.13.

UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp);

This is giving me: 
ERROR : Invalid use of group function.

I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2.

Any ideas?
Please don't tell me to how to do it 4.1!  Please! :)
Roy



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

RE: Complex select statement

[Roy Walker]

Still having a problem with this.  Still have one last thing that isn't working.  This 
is MySQL 4.0.13.

UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - 
table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp);

This is giving me: 
ERROR : Invalid use of group function.

I am trying to update the period_count field for all the records in table_tmp, by 
setting the equal to the count from table_tmp MINUS the count from table2 where the 
id's match and it is the newest record in table2.

Any ideas?
Please don't tell me to how to do it 4.1!  Please! :)

Roy

-Original Message-
From: Rudy Metzger [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 14, 2003 3:49 AM
To: Roy Walker; [EMAIL PROTECTED]
Subject: RE: Complex select statement

I never heard before that you can use a select statement in an arithmetic expression. 
Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) 
should work.

Anyway, it is considered a "subselect" and therefore does not work yet. However, in 
4.1 you should also be able to formulate it like this:

INSERT INTO table2( id, count, period_count, date )
SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1) 
  FROM table1 t1,
   (SELECT count
  FROM table2
 WHERE id = t1.id
 ORDER BY timestamp desc
 LIMIT 1) x

This would also be the way how I would do it in a different DB (e.g. ORACLE).

Cheers
/rudy

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 18:30
To: [EMAIL PROTECTED]
Subject: RE: Complex select statement

Eva,

Thanx for your help.  Still having an issue with this.  I forgot to mention I am 
running MySQL 4.0.13.  Tried both of these statements:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - 
(SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
(count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section.  
Is this illegal syntax?  Would this be considered a subselect?

Thanx,
Roy

-Original Message-
From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 12:30 AM
To: Roy Walker
Subject: RE: Complex select statement

try this:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
(count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

i am not sure, the subselect is ok. if the "id1" alias will not work, then
try to use a variable (... SELECT  @id1:=id ... WHERE [EMAIL PROTECTED] ...).
i just corrected a few syntax mistakes in your query. 

eva

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED]
Sent: Friday, July 11, 2003 7:19 AM
To: [EMAIL PROTECTED]
Subject: Complex select statement


If anyone could tell me what I am doing wrong here, I would greatly
appreaciate it.
 
Have the following tables:
 
table1: id, count
table2: id, count, period_count, date, timestamp
 
Trying to do the following; get all rows from table 1 and insert them into
table2 while setting period_count to count.table1 minus the most recent
entry for that id in count.table2, then set the date field to the previous
day.
 
Here goes:
 
INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1,
(count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';

Sorry if this is easy, but I could not find an example of any mathematic
functions while doing an INSERT ... SELECT.
 
Thanx,
Roy

-- 
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: Complex select statement

[Rudy Metzger]

I never heard before that you can use a select statement in an arithmetic expression. 
Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) 
should work.

Anyway, it is considered a "subselect" and therefore does not work yet. However, in 
4.1 you should also be able to formulate it like this:

INSERT INTO table2( id, count, period_count, date )
SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1) 
  FROM table1 t1,
   (SELECT count
  FROM table2
 WHERE id = t1.id
 ORDER BY timestamp desc
 LIMIT 1) x

This would also be the way how I would do it in a different DB (e.g. ORACLE).

Cheers
/rudy

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 18:30
To: [EMAIL PROTECTED]
Subject: RE: Complex select statement

Eva,

Thanx for your help.  Still having an issue with this.  I forgot to mention I am 
running MySQL 4.0.13.  Tried both of these statements:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - 
(SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
(count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section.  
Is this illegal syntax?  Would this be considered a subselect?

Thanx,
Roy

-Original Message-
From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 12:30 AM
To: Roy Walker
Subject: RE: Complex select statement

try this:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
(count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

i am not sure, the subselect is ok. if the "id1" alias will not work, then
try to use a variable (... SELECT  @id1:=id ... WHERE [EMAIL PROTECTED] ...).
i just corrected a few syntax mistakes in your query. 

eva

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED]
Sent: Friday, July 11, 2003 7:19 AM
To: [EMAIL PROTECTED]
Subject: Complex select statement


If anyone could tell me what I am doing wrong here, I would greatly
appreaciate it.
 
Have the following tables:
 
table1: id, count
table2: id, count, period_count, date, timestamp
 
Trying to do the following; get all rows from table 1 and insert them into
table2 while setting period_count to count.table1 minus the most recent
entry for that id in count.table2, then set the date field to the previous
day.
 
Here goes:
 
INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1,
(count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';

Sorry if this is easy, but I could not find an example of any mathematic
functions while doing an INSERT ... SELECT.
 
Thanx,
Roy

-- 
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: Complex select statement

[Nils Valentin]

2003年 7月 12日 土曜日 01:30、Roy Walker さんは書きました:
> Eva,
>
> Thanx for your help.  Still having an issue with this.  I forgot to mention
> I am running MySQL 4.0.13.  Tried both of these statements:
>
> INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
> (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
>
> INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
> (count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
>
> They both give me an error for the 'SELECT count FROM table2 WHERE id=id1'
> section.  Is this illegal syntax?  Would this be considered a subselect?

YUP

SELECT  SELECT...

is a subselect.

Best regards

Nils Valentin
Tokyo/Japan

>
> Thanx,
> Roy
>
> -Original Message-
> From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 11, 2003 12:30 AM
> To: Roy Walker
> Subject: RE: Complex select statement
>
> try this:
>
> INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
> (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
>
> i am not sure, the subselect is ok. if the "id1" alias will not work, then
> try to use a variable (... SELECT  @id1:=id ... WHERE [EMAIL PROTECTED] ...).
> i just corrected a few syntax mistakes in your query.
>
> eva
>
> -Original Message-
> From: Roy Walker [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 11, 2003 7:19 AM
> To: [EMAIL PROTECTED]
> Subject: Complex select statement
>
>
> If anyone could tell me what I am doing wrong here, I would greatly
> appreaciate it.
>
> Have the following tables:
>
> table1: id, count
> table2: id, count, period_count, date, timestamp
>
> Trying to do the following; get all rows from table 1 and insert them into
> table2 while setting period_count to count.table1 minus the most recent
> entry for that id in count.table2, then set the date field to the previous
> day.
>
> Here goes:
>
> INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM
> table1, (count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
> id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';
>
> Sorry if this is easy, but I could not find an example of any mathematic
> functions while doing an INSERT ... SELECT.
>
> Thanx,
> Roy

-- 
---
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: Complex select statement

[Roy Walker]

I am using 4.0.13, not 4.0.1.  Good to know what was wrong with the
previous statement.  How bout this one?

DROP TABLE IF EXISTS table_tmp;

CREATE TABLE table_tmp (
  `id` varchar(12) NOT NULL default '0',
  `count` mediumint(15) NOT NULL default '0',
  `period_count` mediumint(15) NOT NULL default '0',
  `date` date NOT NULL default '-00-00',
  `timestamp` timestamp(14) NOT NULL,
) TYPE=MyISAM;

INSERT INTO table_tmp (id,count,date) SELECT id, count, (TO_DAYS(NOW()
-1)) FROM table1;

UPDATE table_tmp SET period_count = (count - SELECT count FROM table2
WHERE id.table_tmp=id.table2 AND WHERE MAX(timestamp.table2));

INSERT INTO table2 (id,count,period_count,date,timestamp) SELECT
id,count,period_count,date,timestamp FROM table_tmp;

DROP TABLE table_tmp;


I get an error on the UPDATE command.

Any ideas?

Thanx,
Roy

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 11:52 AM
To: [EMAIL PROTECTED]
Subject: Re: Complex select statement

"Roy Walker" <[EMAIL PROTECTED]> wrote:
> 
> Thanx for your help.  Still having an issue with this.  I forgot to
mention I am running MySQL 4.0.13.  Tried both of these statements:
> 
> INSERT INTO table2 (id,count,period_count,date) SELECT id as id1,
count, (count - (SELECT count FROM table2 WHERE id=id1 order by
timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
> 
> INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id,
count,
> (count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp
desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
> 
> They both give me an error for the 'SELECT count FROM table2 WHERE
id=id1' section.  Is this illegal syntax?  Would this be considered a
subselect?

1. 4.0.1 is too old and unstable version.
2. You can't insert data into the table, which you use in the SELECT
part too (table2).

Retrieve data from table2 to the temporary table and then insert data to
the table2 using table1 and temporary table.




-- 
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]


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

Re: Complex select statement

[Egor Egorov]

"Roy Walker" <[EMAIL PROTECTED]> wrote:
> 
> Thanx for your help.  Still having an issue with this.  I forgot to mention I am 
> running MySQL 4.0.13.  Tried both of these statements:
> 
> INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - 
> (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
> 
> INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
> (count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
> limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
> 
> They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section.  
> Is this illegal syntax?  Would this be considered a subselect?

1. 4.0.1 is too old and unstable version.
2. You can't insert data into the table, which you use in the SELECT part too (table2).

Retrieve data from table2 to the temporary table and then insert data to the table2 
using table1 and temporary table.




-- 
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: Complex select statement

[Roy Walker]

Eva,

Thanx for your help.  Still having an issue with this.  I forgot to mention I am 
running MySQL 4.0.13.  Tried both of these statements:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - 
(SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
(count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section.  
Is this illegal syntax?  Would this be considered a subselect?

Thanx,
Roy

-Original Message-
From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 12:30 AM
To: Roy Walker
Subject: RE: Complex select statement

try this:

INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
(count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;

i am not sure, the subselect is ok. if the "id1" alias will not work, then
try to use a variable (... SELECT  @id1:=id ... WHERE [EMAIL PROTECTED] ...).
i just corrected a few syntax mistakes in your query. 

eva

-Original Message-
From: Roy Walker [mailto:[EMAIL PROTECTED]
Sent: Friday, July 11, 2003 7:19 AM
To: [EMAIL PROTECTED]
Subject: Complex select statement


If anyone could tell me what I am doing wrong here, I would greatly
appreaciate it.
 
Have the following tables:
 
table1: id, count
table2: id, count, period_count, date, timestamp
 
Trying to do the following; get all rows from table 1 and insert them into
table2 while setting period_count to count.table1 minus the most recent
entry for that id in count.table2, then set the date field to the previous
day.
 
Here goes:
 
INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1,
(count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';

Sorry if this is easy, but I could not find an example of any mathematic
functions while doing an INSERT ... SELECT.
 
Thanx,
Roy

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