a tricky join
Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen
Re: a tricky join
Helen M Hudson ha scritto: So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! I do not know if i can give you the light because my english is not as good as I'd like. If i've understood your question, you can try this SQL statement: SELECT max( `date` ) AS 'date', sum( `amount` ) AS 'amount' FROM `Prova` WHERE `order` =100 GROUP BY `order` LIMIT 0 , 30 Enjoy and please tell me if it solves your problem... -- vittorio zuccalà Finconsumo Banca SPA [EMAIL PROTECTED] Tel: 011-6319464 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Helen M Hudson schrieb: Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen Well... MAX(date) AS latest_date_on_order, SUM(amount) FROM table GROUP BY date is it that what you looked for? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 and I want to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 3/1/01 | 101 | 12500 Helen Quoting Helen M Hudson [EMAIL PROTECTED]: So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 Not tested SELECT MAX(Date), order_ref, SUM(amount) FROM table WHERE order_ref=100 GROUP BY amount That should sum all the amounts that is distinct, and have a order ref of 100. I'm not sure if MAX(date) will be accepted - but there are better ways to select the date depending on the column type.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Hi, 2006/6/21, Helen M Hudson [EMAIL PROTECTED]: Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 What you're asking does not involve join, but is a trick called group-wise maximum. Depending on your version of MySQL, there are several options to resolve this : http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html (I haven't tested it) : SELECT date, order_ref, sum(amount) FROM table s1 WHERE date=(SELECT MAX(s2.date) FROM table s2 WHERE s1.order_ref= s2.order_ref) GROUP BY s1.order_ref; the subquery get you the maximum date for each order_ref, and then you do the sum of this date. Was it what you were looking for ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Join with Animal-DB
Martin Rytz wrote: [...] With which join can I show the animals with all pics for this animal (even if there are more than one pic for an animal)? SELECT animal,pic FROM animal_pic,animal,pic WHERE animal.id = animalid AND pic.id = picid This will list all animals which have pictures, with all their pictures. If you wanted a special animal, you could just add AND animal = 'Turtle'. The above shorthand way of writing a join is the same as the following more explicit join, note that the WHERE clause is used only for result conditions, the join conditions are moved to the ON parts of the joins: SELECT animal.animal,pic.pic FROM animal_pic INNER JOIN animal ON animal.id = animal_pic.animalid INNER JOIN pic ON pic.id = animal_pic.picid WHERE animal = 'Turtle' If you also wanted animals _without_ pictures in the list, you could use a LEFT JOIN: SELECT animal.animal,pic.pic FROM animal_pic INNER JOIN animal ON animal.id = animal_pic.animalid LEFT JOIN pic ON pic.id = animal_pic.picid Finally, if you also wanted pictures without animals, you could LEFT JOIN both tables: SELECT animal.animal,pic.pic FROM animal_pic LEFT JOIN animal ON animal.id = animal_pic.animalid LEFT JOIN pic ON pic.id = animal_pic.picid URL: http://dev.mysql.com/doc/mysql/en/join.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Join with Animal-DB
SELECT animal, pic from animal AS a, pic as p, animal_pic as ap WHERE animal = 'penguin' AND ap.animalid = a.id and ap.picid = p.id On Wed, 2005-02-16 at 03:32, Martin Rytz wrote: Hi SQL-Community I need once more your help, I have a tricky problem with my animal-db. I have a table with animals and a table with animal-pics. I have a third table. In this table you can see, which pic belongs to which animal: CREATE TABLE animal ( id int(11) NOT NULL auto_increment, animal varchar(250) NULL, PRIMARY KEY (id) ); CREATE TABLE pic ( id int(11) NOT NULL auto_increment, pic varchar(250) NULL, PRIMARY KEY (id) ); CREATE TABLE animal_pic ( animalid int(11) NULL , picid int(11) NULL ); The problem is now, that there can be n pics for an animal! With which join can I show the animals with all pics for this animal (even if there are more than one pic for an animal)? Thank you for you ideas! Martin -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
