a lil sql help please.
Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders The query produces the following results: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +1 + + oid1 + 2006-07-08 +2 + + oid1 + 2006-07-08 +3 + + oid5 + 2006-07-08 +7 + + oid5 + 2006-07-08 +1 + + oid4 + 2006-07-08 +1 + + oid4 + 2006-07-08 +2 + + oid4 + 2006-07-08 +1 + +++ This is actually right. However, ideally what I'm wanting is this: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +6 + + oid5 + 2006-07-08 +8 + + oid4 + 2006-07-08 +4 + +++ Note the order_piececount column. What do I need to do to my SQL statement to perform this action ? My guess that I need to perform a secondary query inside the statement to get the computed value of order_piececount. Anyone ? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a lil sql help please.
At 18:51 9.7.2006, you wrote: Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders SELECT tbe_orders.order_id, tbe_orders.order_date, sum(tbe_orders.order_piececount) FROM tbe_orders GROUP BY tbe_orders.order_id or SELECT tbe_orders.order_id, tbe_orders.order_date, sum(tbe_orders.order_piececount) FROM tbe_orders GROUP BY tbe_orders.order_id, tbe_orders.order_date depending whether date matters or not. Regards, Dundo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a lil sql help please.
m i l e s wrote: Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders The query produces the following results: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +1 + + oid1 + 2006-07-08 +2 + + oid1 + 2006-07-08 +3 + + oid5 + 2006-07-08 +7 + + oid5 + 2006-07-08 +1 + + oid4 + 2006-07-08 +1 + + oid4 + 2006-07-08 +2 + + oid4 + 2006-07-08 +1 + +++ This is actually right. However, ideally what I'm wanting is this: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +6 + + oid5 + 2006-07-08 +8 + + oid4 + 2006-07-08 +4 + +++ Note the order_piececount column. What do I need to do to my SQL statement to perform this action ? My guess that I need to perform a secondary query inside the statement to get the computed value of order_piececount. Anyone ? M i l e s. SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, SUM(tbe_orders.order_piececount ) FROM tbe_orders GROUP BY order_id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help please
SQL guru's, I could use some help writing a bit of SQL. There's 3 tables: orderable_parts partID varchar, topCatID int, # top level category ID ... part_attributes partID varchar, attName varchar, attValue varchar, ... topcatattributevalues tcavID int, topCatID int, attName varchar, attValue varchar, ... orderable_parts has parts available on our web site. Theres about 40,000 of them part_attributes are related to parts. Color, size etc. ~150,000 rows topcatattributevalues is a list of all the distinct part attributes available in each top level category. They're used for web searches and for product managers to edit orderable_parts, ~100,000 rows I'm trying to build a function to delete records from topcatattributevalues that are not used in any orderable_part. Either 1 delete statement or an update status=-1 and a delete where status=-1 would work. But it looks to me like I need a 3 way outer join and I can't seem to get it to work. I tried a variety of statements that look something like: update topcatattributevalues t left outer join orderable_parts o on (t.topCatID=o.topCatID), left outer join part_attributes p on (t.attName=p.attName and t.attValue=p.attValue and o.partID=p.partID) set t.status=-1 where o.partID is null I can't seem to get it. Any assistance would be greatly appreciated. Thanks. Dave. _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help, Please...
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at the same
value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid and bid,
but it does not return the bidderId correctly.
Can anybody help?
Cheers
Carl
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23 21:42:50',
'2002-04-30 11:30:00', 'active');
INSERT INTO sa_auction (id, start, end, state) VALUES (2, '2002-01-23 21:42:50',
'2002-02-09 06:30:00', 'expired');
INSERT INTO sa_auction (id, start, end, state) VALUES (3, '2002-03-23 21:42:50',
'2002-07-05 09:00:00', 'waiting');
INSERT INTO sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting');
#
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL auto_increment,
auctionId int(11) NOT NULL default '0',
cellId tinyint(4) NOT NULL default '0',
bid int(11) NOT NULL default '0',
bidderId mediumint(9) NOT NULL default '0',
bidtime timestamp(14) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_bid`
#
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (1, 1, 5, 1,
1, 20020128225421);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (2, 1, 5, 2,
2, 20020128225424);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
15, 2, 20020213214856);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (4, 1, 5, 3,
4, 20020213215649);
This e-mail and any attachments are confidential. If you are not the intended
recipient, please notify us immediately by reply e-mail and then delete this message
from your system. Do not copy this e-mail or any attachments, use the contents for any
purpose, or disclose the contents to any other person: to do so could be a breach of
confidence.
-
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: SQL Help, Please...
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at
the same value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid
and bid, but it does not return the bidderId correctly.
Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
Cheers
Carl
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
'2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting'); #
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL auto_increment,
auctionId int(11) NOT NULL default '0',
cellId tinyint(4) NOT NULL default '0',
bid int(11) NOT NULL default '0',
bidderId mediumint(9) NOT NULL default '0',
bidtime timestamp(14) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_bid`
#
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES
(1, 1, 5, 1, 1, 20020128225421); INSERT INTO sa_bid (id, auctionId, cellId,
bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 2, 20020128225424); INSERT INTO
sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
15, 2, 20020213214856); INSERT INTO sa_bid (id, auctionId, cellId, bid,
bidderId, bidtime) VALUES (4, 1, 5, 3, 4, 20020213215649);
This e-mail and any attachments are confidential. If you are not the
intended recipient, please notify us immediately by reply e-mail and then
delete this message from your system. Do not copy this e-mail or any
attachments, use the contents for any purpose, or disclose the contents to
any other person: to do so could be a breach of confidence.
-
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,
Re: SQL Help, Please...
Hi,
At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at
the same value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid
and bid, but it does not return the bidderId correctly.
Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
This behaviour is well documented in the manual. There is a very practical
reason to allow this behaviour. Suppose for efficiency reasons data is
denormalized
and for example, id,name and some other particulars are all kept in the same
table . If the database was very strict that all the columns selected
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these
fields in
the intermediate ordering phase of the query execution. Surely this will be
inefficient in both time and space. But with the 'incorrect' behaviour of
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id
field.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
Cheers
Carl
The problem can be solved by using temporary tables.
Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;
Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid =
b.bid)
group by t1.auctionid,t1.cellid,t1.bid
Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid =
b.bid and t2.bidtime = b.bidtime
I hope there may be better and simpler ways to achieve the objective.
Surely correlated subquery and derived table features might have been good
features for such
situations.
Anvar.
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
'2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting'); #
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL
