Re: Trying to simplify an sql query

On 17 Apr 2006 18:07:03 -0700, "dn.usenet" <dn.usenet@xxxxxxxxx>
wrote:


I am using the following sql query which I feel could be simplified,
but I don't know how. For t1, fields (project + year) form the Pr Key.

select t1.location from t1 aa, t2 t2
where aa.project = t2.project and aa.year = t2.year and aa.month =
t2.month
and exists
(select 1 from t1 bb where aa.project = bb.project and aa.year =
bb.year
having count(*) = 1
group by bb.project, bb.year, bb.month
)

I think I have tried the following construct for the subquery
but it hanged the database; maybe I tried a query which is slightly
different than the one I am quoting below, but I don't want
to risk hanging the database again.

(select 1 from null having count(*) = 1
group by aa.project, aa.year, aa.month)

Thanks in advance.

logically speaking the subquery should return 1 if any record exists
in any month for the combination of project and year.
As month is no part of the primary key and exists will invariably
select only 1 record, you don't need the month and you don't need the
count(*) and the group by.

Hth

--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • RE: Recursive SQL query?
    ... I don't know if other database vendors support this. ... Subject: Recursive SQL query? ... You shouldn't really need to go recursing through a data ... key that references that same table's primary key. ...
    (perl.dbi.users)
  • RE: Expression Builder basic question on syntax / Or / how to incorpor
    ... it is always best to avoid unnecessary hits on the database. ... Use only the control name without the Me. ... runtime error on the textbox. ... This SQL query works, ...
    (microsoft.public.access.formscoding)
  • Re: Noob question on how to use an insert query in a command button
    ... in mm/dd/yyyy format (regardless of what your Short Date format have been ... Dim dbLocation 'Location of our Access database file ... Set objADO = CreateObject'Create an ADO connection ... objADO.Execute 'Execute this SQL query ...
    (microsoft.public.access.forms)
  • Re: cgi ordering a table with sql
    ... table, through the SQL query. ... often only values may be replaced by placeholders, ... the prepare should give the database ... implemented within the Perl layer instead of passing the prepared ...
    (perl.beginners)
  • Re: Binding a grid to master/detail related datatables
    ... build my command objects, but it won't update the database." ... recommend Stored Procedures for accessing your database, ... The above SQL Query should give you the data you need. ... Say I have three datatables that are linked ...
    (microsoft.public.dotnet.general)
Loading