Sub queries or cursors 2

[IanStrange]

Hi
Where I work uses sql server however we have some legacy systems on Oracle and I am not that familiar with the writting of queries.
I have to make and example up as my boss does not wish for me to give away any system info.
ok
table 1 (car usage)
salesmanid, year, carid

table 2 (type of car)
carid, cardesc

we need to see which sales man has used over 6 different cars in 1 year and the car desc of each of these cars.

I have tried using a subquery but have got every car he has ever used.
this was the query

SELECT dbo.car_usage.salesmanid, dbo.car_usage.car_id, dbo.car.description
FROM dbo.car_usage INNER JOIN
dbo.car ON dbo.car_usage.car_id = dbo.car.car_id
WHERE (dbo.car_usage.salesmanid IN
(SELECT dbo.car_usage.salesmanid FROM dbo.car_usage
GROUP BY dbo.car_usage.salesmanid,dbo.car_usage.[year]
HAVING (COUNT(dbo.car_usage.car_id) > 6)))
ORDER BY dbo.car_usage.salesmanid

thanks for any help
Ian

No man is an island but 6 tied together make quite a good raft.

 

[AOLEURO]

Hi Ian

First I build some tables:

drop table car_usage;
drop table car;

I can run the script repeated times.

create table car
  (carid number(5) constraint car_carid_nn not null,
   cardesc varchar2(30),
   constraint car_carid_pk primary key (carid)
   )
   organization index
   including cardesc
   overflow;

I create the table organized as an index because I think you got lots of cars, and it’s waste of I/O if separated index and table data.

insert into car values( 1, 'Ford T' );
insert into car values( 2, 'Ford T2' );
insert into car values( 3, 'Ford T3' );
insert into car values( 4, 'Ford T4' );
insert into car values( 5, 'Ford T5' );
insert into car values( 6, 'MG T6' );
insert into car values( 7, 'MG T7' );
insert into car values( 8, 'MG T8' );
insert into car values( 9, 'MG T9' );
insert into car values( 10, 'MG T10' );

Well no data no go…

create table car_usage 
  (salesmanid number(5) constraint carusage_salesmanid_nn not null, 
   year number(4) constraint carusage_year_nn not null, 
   carid number(5) constraint carusage_carid_nn not null,
   constraint carusage_carid_fk foreign key (carid) references car(carid) on delete cascade
   );

It’s not possible to use a primary index on this table because all columns is part of the key – and maybe even more than one of the same row (non-unique).

insert into car_usage values(101, 2002, 1);
insert into car_usage values(101, 2002, 2);
insert into car_usage values(101, 2002, 3);
insert into car_usage values(101, 2002, 4);

insert into car_usage values(101, 2003, 4);
insert into car_usage values(101, 2003, 3);
insert into car_usage values(101, 2003, 2);
insert into car_usage values(101, 2003, 4);
insert into car_usage values(101, 2003, 10);
insert into car_usage values(101, 2003, 9);
insert into car_usage values(101, 2003, 7);

insert into car_usage values(102, 2002, 1);
insert into car_usage values(102, 2002, 2);
insert into car_usage values(102, 2002, 3);
insert into car_usage values(102, 2002, 4);
insert into car_usage values(102, 2002, 5);

insert into car_usage values(102, 2003, 1);
insert into car_usage values(102, 2003, 2);
insert into car_usage values(102, 2003, 3);
insert into car_usage values(102, 2003, 4);
insert into car_usage values(102, 2003, 5);

Well no data no go…

commit;

And now the code:

select cu.salesmanid, cu.year, c.cardesc
from car c, car_usage cu
where ( cu.salesmanid, cu.year ) in ( select salesmanid, year 
                                         from car_usage cu 
                                         group by salesmanid, year 
                                         having count(*) > 6)
and   c.carid = cu.carid
order by cu.salesmanid, cu.year, cu.carid;

And if you need to reduce years, then you could change the code to:

select cu.salesmanid, cu.year, c.cardesc
from car c, car_usage cu
where ( cu.salesmanid, cu.year ) in ( select salesmanid, year 
                                        from car_usage cu

where year >= to_number(to_char(sysdate,'yyyy'),'9999')-2

                                        group by salesmanid, year 
                                        having count(*) > 6)
and   c.carid = cu.carid
order by cu.salesmanid, cu.year, cu.carid;

I think this code is generic.


Regards
Allan
Icq: 346225948