|
Home > Archive > MySQL ODBC Connector > May 2005 > basic sql join question...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
basic sql join question...
|
|
|
|
| Philip George 2005-05-29, 3:23 am |
| guess i'll just ask:
here are the 2 tables of interest:
mysql> select * from ticket_details;
+--------------------------------------
+--------------------------------------+----------+
| ticket | product
| quantity |
+--------------------------------------
+--------------------------------------+----------+
| 9f2d7b86-213d-1028-88b7-09e76b61a517 |
85d0d5bc-213c-1028-88b7-09e76b61a517 | 1 |
| 9f2d7b86-213d-1028-88b7-09e76b61a517 |
ad67557e-213c-1028-88b7-09e76b61a517 | 3 |
| ec04c91e-2142-1028-88b7-09e76b61a517 |
60e766f8-213c-1028-88b7-09e76b61a517 | 7 |
| ec04c91e-2142-1028-88b7-09e76b61a517 |
a4341a8c-213c-1028-88b7-09e76b61a517 | 2 |
+--------------------------------------
+--------------------------------------+----------+
mysql> select * from product;
+--------------------------------------+--------+-------+
| id | name | price |
+--------------------------------------+--------+-------+
| 60e766f8-213c-1028-88b7-09e76b61a517 | banana | 1.98 |
| 85d0d5bc-213c-1028-88b7-09e76b61a517 | orange | 0.97 |
| a4341a8c-213c-1028-88b7-09e76b61a517 | apple | 0.89 |
| ad67557e-213c-1028-88b7-09e76b61a517 | pear | 1.09 |
+--------------------------------------+--------+-------+
here's a functional join that retrieves some specifics from a given
ticket #:
select ticket_details.quantity,
product.name,
product.price,
(product.price * ticket_details.quantity) as subtotal
from product,
ticket_details
where ticket_details.ticket = '9f2d7b86-213d-1028-88b7-09e76b61a517'
AND
ticket_details.product = product.id
;
+----------+--------+-------+----------+
| quantity | name | price | subtotal |
+----------+--------+-------+----------+
| 1 | orange | 0.97 | 0.97 |
| 3 | pear | 1.09 | 3.27 |
+----------+--------+-------+----------+
how can i also show a grand total for the ticket (without changing the
table structure)? i've tried with no success to use SUM() to do this.
would i even use SUM()?
if i could refer to the resultant table above in a subsequent select,
that would be ideal. is there a way to do that (something like select
SUM(@@result.subtotal);)?
thanks.
- philip
On May 28, 2005, at 11:36 PM, Philip George wrote:
> is it okay to post a basic sql join question to this list?
>
> if not, please point me to a list for these types of questions.
>
> thanks.
>
> - philip
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=invest@juun.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jochem van Dieten 2005-05-29, 3:23 am |
| On 5/29/05, Philip George <invest@juun.com> wrote:
>=20
> +----------+--------+-------+----------+
> | quantity | name | price | subtotal |
> +----------+--------+-------+----------+
> | 1 | orange | 0.97 | 0.97 |
> | 3 | pear | 1.09 | 3.27 |
> +----------+--------+-------+----------+
>=20
> how can i also show a grand total for the ticket (without changing the
> table structure)? i've tried with no success to use SUM() to do this.
> would i even use SUM()?
This has got nothing to do with joins, you can have the same problem
in a single table:
http://dev.mysql.com/doc/mysql/en/g...-modifiers.html
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| Jochem van Dieten 2005-05-29, 3:23 am |
| On 5/29/05, Philip George wrote:
> On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote:
>=20
> already read that. the join in my example is more complicated than
> anything depicted on that page.
The join is irrelevant. Your join returns a resultset and you can just
pretent that resultset is a single table:
SELECT field1, field2, field3
FROM (very complicated join) AS simpletable
GROUP BY ...
WITH ROLLUP
Just copy-pate your join into this and fix the fieldnames.
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| Philip George 2005-05-29, 3:23 am |
| > The join is irrelevant. Your join returns a resultset and you can just
> pretent that resultset is a single table:
>
> SELECT field1, field2, field3
> FROM (very complicated join) AS simpletable
> GROUP BY ...
> WITH ROLLUP
>
> Just copy-pate your join into this and fix the fieldnames.
>
aaahhh....
okay, i'm close:
mysql> select ticket_details.quantity,
product.name,
product.price,
(product.price * ticket_details.quantity) as subtotal
from product,
ticket_details
where ticket_details.ticket =
'9f2d7b86-213d-1028-88b7-09e76b61a517' AND
ticket_details.product = product.id
group by subtotal
with rollup
;
+----------+--------+-------+----------+
| quantity | name | price | subtotal |
+----------+--------+-------+----------+
| 1 | orange | 0.97 | 0.97 |
| 3 | pear | 1.09 | 3.27 |
| 3 | pear | 1.09 | NULL |
+----------+--------+-------+----------+
the NULL is in the wrong column. where is my mistake?
thanks, jochem.
- philip
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jochem van Dieten 2005-05-29, 3:23 am |
| On 5/29/05, Philip George wrote:
> On 5/29/05, Philip George wrote:
>=20
> actually i should say that there are no examples of SUM() or AVG() --
> or any of the other GROUP BY functions -- that are used with a join on
> that page.
You can't always solve your problems by following an example.
Sometimes you have to recognize the patterns, apply your own knowledge
and extend the examples. The MySQL documentation, with its focus on
examples instead of formal definitions, isn't the easiest for that, so
I would strongly suggest learning SQL from a source that pays more
attention to formal definitions.
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| mfatene@free.fr 2005-05-29, 8:23 pm |
| re-submitted :
re-send :
Hi,
you can use mysql variables :
set @total:=0;
select name,price,quantity,
price*quantity as
subtotal,@total:=@to
tal+price*quantity
from fruits;
+--------+-------+----------+----------+-------------------------------+
| name | price | quantity | subtotal | @total:=@total+price
*quantity |
+--------+-------+----------+----------+-------------------------------+
| orange | 1 | 2 | 2 | 2 |
| banana | 1 | 4 | 4 | 6 |
+--------+-------+----------+----------+-------------------------------+
The total column will be incremented by subtotal in each row.
Mathias
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Philip George 2005-05-29, 8:23 pm |
| On May 29, 2005, at 2:34 PM, mfatene@free.fr wrote:
> you can use mysql variables :
>
> set @total:=0;
> select name,price,quantity,
price*quantity as
> subtotal,@total:=@to
tal+price*quantity
> from fruits;
>
>
> +--------+-------+----------+----------
> +-------------------------------+
> | name | price | quantity | subtotal |
> @total:=@total+price
*quantity |
>
> +--------+-------+----------+----------
> +-------------------------------+
> | orange | 1 | 2 | 2 |
> 2 |
> | banana | 1 | 4 | 4 |
> 6 |
>
> +--------+-------+----------+----------
> +-------------------------------+
>
> The total column will be incremented by subtotal in each row.
actually, i need a grand total of the entire ticket:
1 orange x 0.97 = 0.97
+ 3 pears x 1.09 = 3.27
____________________
_____
4.24 < grand total
i think you're right that variables can be used to do this, but i can't
figure out how to get a grand total for the entire sale.
but, i wouldn't scratch your head about it too much. i sort of gave up
on the idea in favor of doing the calculation in the client code at
runtime after selecting all the pertinent data.
thanks though. :)
- philip
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mfatene@free.fr 2005-05-29, 8:23 pm |
| Hi,
you can use mysql variables :
set @total:=0;
select name,price,quantity,
price*quantity as
subtotal,@total:=@to
tal+price*quantity
from fruits;
+--------+-------+----------+----------+-------------------------------+
| name | price | quantity | subtotal | @total:=@total+price
*quantity |
+--------+-------+----------+----------+-------------------------------+
| orange | 1 | 2 | 2 | 2 |
| banana | 1 | 4 | 4 | 6 |
+--------+-------+----------+----------+-------------------------------+
The total column will be incremented by subtotal in each row.
Mathias
Selon Philip George <invest@juun.com>:
> actually, i've decided this is sort of a moot point, since i can do
> this calculation in the client app.
>
> no sql required.
>
> thanks.
>
> - philip
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mfatene@free.fr
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mfatene@free.fr 2005-05-30, 3:23 am |
| Hi Philip,
what yoy called gand total is in @total for evevy row.
you can just play :
select @total as "grand total";
that's all !
Mathias
Selon Philip George <invest@juun.com>:
[color=darkred]
> On May 29, 2005, at 2:34 PM, mfatene@free.fr wrote:
>
>
>
> actually, i need a grand total of the entire ticket:
>
> 1 orange x 0.97 = 0.97
> + 3 pears x 1.09 = 3.27
> ____________________
_____
> 4.24 < grand total
>
>
> i think you're right that variables can be used to do this, but i can't
> figure out how to get a grand total for the entire sale.
>
> but, i wouldn't scratch your head about it too much. i sort of gave up
> on the idea in favor of doing the calculation in the client code at
> runtime after selecting all the pertinent data.
>
> thanks though. :)
>
> - philip
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mfatene@free.fr
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| mfatene@free.fr 2005-05-30, 3:23 am |
| re-send :
Hi,
you can use mysql variables :
set @total:=0;
select name,price,quantity,
price*quantity as
subtotal,@total:=@to
tal+price*quantity
from fruits;
+--------+-------+----------+----------+-------------------------------+
| name | price | quantity | subtotal | @total:=@total+price
*quantity |
+--------+-------+----------+----------+-------------------------------+
| orange | 1 | 2 | 2 | 2 |
| banana | 1 | 4 | 4 | 6 |
+--------+-------+----------+----------+-------------------------------+
The total column will be incremented by subtotal in each row.
Mathias
> Selon Philip George <invest@juun.com>:
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|