Re: Advanced SQL statements writing
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Thu, 16 Apr 2009 09:26:48 -0500
Message-ID: <YcHFl.18983$as4.15308_at_nlpi069.nbdc.sbc.com>
Phper wrote:
> I wrote this SQL statement:
>
> SELECT *
> FROM ((SELECT piecework_id,1+count(*)
> FROM view
> GROUP BY piecework_id) AS view1
> LEFT OUTER JOIN
> (SELECT piecework_id, sum(status)
> FROM submission
> GROUP BY piecework_id) AS submission1
> on view1.piecework_id=submission1.piecework_id);
>
> It can be executed successfully.
> This is the result.
> piecework_id 1+count(*) piecework_id sum(status)
> 8 2 8 NULL
> 989 2 NULL NULL
>
> But I want the result to be
> piecework_id 1+count(*) sum(status)
> 8 2 NULL
> 989 2 NULL
>
> How can I achieve this?
Date: Thu, 16 Apr 2009 09:26:48 -0500
Message-ID: <YcHFl.18983$as4.15308_at_nlpi069.nbdc.sbc.com>
Phper wrote:
> I wrote this SQL statement:
>
> SELECT *
> FROM ((SELECT piecework_id,1+count(*)
> FROM view
> GROUP BY piecework_id) AS view1
> LEFT OUTER JOIN
> (SELECT piecework_id, sum(status)
> FROM submission
> GROUP BY piecework_id) AS submission1
> on view1.piecework_id=submission1.piecework_id);
>
> It can be executed successfully.
> This is the result.
> piecework_id 1+count(*) piecework_id sum(status)
> 8 2 8 NULL
> 989 2 NULL NULL
>
> But I want the result to be
> piecework_id 1+count(*) sum(status)
> 8 2 NULL
> 989 2 NULL
>
> How can I achieve this?
you are really starting to annoy me. you have asked so many very basic questions, it makes me wonder if you are just a troll. If you really do not understand what the heck you are doing, you might want to find another line of work or go to school.
You achieve this by breaking you query down into parts, testing and more testing. you have too many sub-selects which is another indication you are clueless. Get a good book on SQL, start up an Oracle database and start testing. Received on Thu Apr 16 2009 - 09:26:48 CDT