Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Help Needed
Hi Jay
I tried your SQL but selecting the three columns always results in a cartesian product on columns two and three no matter what restrictions you might put in the Having clause.
Van
Jason Jay Weiland wrote in message
<363A5149.5C6BFA5B_at_uclink4.berkeley.edu>...
>Van,
>
> Try this one:
>
>SELECT
> request.request R1, sum(draft.amount) S1,
> sum(detail.quantity * detail.rate) S2
>FROM
> request, draft, detail
>WHERE
> request.request=draft.request
> AND request.request=detail.request
>GROUP BY
> request.request
>HAVING
> sum(draft.amount) <> sum(detail.quantity * detail.rate)
>
>
>Jay!!!
>
>
>Van Messner wrote:
>
>> HELP
>> I have three small tables Request, Detail, Draft. Request provides a
>> foreign key (column request) to Detail and to Draft. The queries below
work
>> fine but I want to combine the two result sets to pick up differences -
i.e.
>> the "1610" and "1310".
>> I want one to end up with one result set (R1, S1, S2) or (R1, S1, R2,
S2)
>> whichever is easier. I've tried a lot of things with no success. How do
I
>> do this? It seems like this must be fairly basic but I'm missing
something.
>> Many thanks
>>
>> SELECT
>> request.request R1,
>> sum(draft.amount) S1
>> FROM
>> request,
>> draft
>> WHERE
>> request.request=draft.request
>> GROUP BY
>> request.request;
>> R1 S1
>> ------- ----------
>> 22828 110.25
>> 23871 1610
>> 25611 68.9
>> 27148 150
>>
>> SELECT
>> request.request R2,
>> sum(detail.quantity * detail.rate) S2
>> FROM
>> request,
>> detail
>> WHERE
>> request.request = detail.request
>> GROUP BY
>> request.request
>> R2 S2
>> ------- ----------
>> 22828 110.25
>> 23871 1310
>> 25611 68.9
>> 27148 150
>
Received on Mon Nov 02 1998 - 16:17:25 CST