Re: posible to simply use select in this case

Q1
select m.id, c.club, c.price*m.quantity total from club c, member m
where c.club = m.club

   	ID	CLUB	TOTAL
1	1000	1	38
2	1001	1	0
3	1002	2	200
4	1002	1	57

Q2.
select c.club, sum(c.price*m.quantity) total from club c, member m
where c.club = m.club
group by c.club

   	CLUB	TOTAL
1	1	95
2	2	200

On 21 Feb 2003 07:01:54 -0800, johnwu_at_yorku.ca (John) wrote:

>I'm wondering whether it's possible to use select for the following
>case:
>
>create table member (id integer,club integer, quantity integer,foreign
>key(club) references club,primary key(id,club));
>create table club (club integer primary key,price integer);
>
>if data in member
>1000 1 2
>1001 1 0
>1002 2 10
>1002 1 3
>
>data in club
>1 19
>2 20
>
>how to use select to show
>
>1. id club total
>where total = quantity*price, like
> 1000 1 38
> 1001 1 0
> 2002 2 200
> 2002 1 57
>
>
>2. club total
>where group by club and total = quantities of all members in same club
>* price?
> 1 95
> 2 200
>
>Thanks lots
  Received on Sat Feb 22 2003 - 15:44:51 CET