Re: Query help required please

On Sun, 27 Oct 2002 14:43:25 -0000, "Steve" <hunt.s_at_blueyonder.co.uk> wrote:

>I am attempting to find which Customers (Names) have commenced a hire
>contract as follows:-
>
>a) Ever.
>b) Anytime last month and,
>c) Anytime this month.
>
>My tables are as follows:
>
>ClientId Name Parent ClientType
>-------- ------------------------------ ------ ----------
>1500 Sydney Symphony Orchestra T
>1501 Melbourne symphony Orchestra T
>1502 Daves Didjeridoo Duo P
>1503 Southern Cross String Quartet 1501 P
>1504 Barcaldine Barbershop Quartet 1501 P
>1505 Blue Room Quintet 1500 P
>1506 Wallerawang Whistlers P
>
>ContractId ClientId HireStart HireReturn
>---------- -------- --------- ----------
>MU15 1505 10-AUG-02 16-SEP-02
>MU16 1504 21-AUG-02 27-SEP-02
>MU17 1503 04-SEP-02 30-SEP-02
>MU18 1501 01-OCT-02 15-OCT-02
>MU19 1500 07-OCT-02 31-OCT-02
>MU20 1506 18-NOV-02 30-NOV-02
>MU21 1503 20-NOV-02 01-DEC-02
>MU22 1505 15-NOV-02 05-DEC-02
>MU23 1506 01-DEC-02 14-DEC-02
>MU24 1500 02-DEC-02 21-DEC-02
>MU25 1503 16-DEC-02 02-JAN-03
>MU26 1501 17-DEC-02 10-JAN-03
>MU27 1503 03-JAN-03 31-JAN-03
>MU28 1504 10-JAN-03 24-JAN-03
>
>I have tried the query below but it lists all matches. Is there a way of
>avoiding displaying duplicates?
>
>Select Clients.Name,
> HireContracts.ClientId
>from Clients, HireContracts
>where Clients.ClientId = HireContracts.ClientId;
>
>Any help with b) and c) very much welcome.
>
>Many thanks in advance.
>
>Mailto: hunt.s_at_blueyonder.co.uk
>

Homework alert!
But anyway
a)
select clients.name
from clients c
where exists
(select 'x'
 from hirecontracts hc
 where hc.clienid = c.clientid
)
b)
select clients.name
from clients c
where exists
(select 'x'
 from hirecontracts hc
 where hc.clienid = c.clientid
 and hirestart between add_months(trunc(sysdate,'MON'),-1) and last_day(add_months((trunc(sysdate,'MON'),-1)) )
c)
select clients.name
from clients c
where exists
(select 'x'
 from hirecontracts hc
 where hc.clienid = c.clientid
 and hirestart>= trunc(sysdate, 'MON')
)

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Oct 27 2002 - 12:01:20 CST