Home » SQL & PL/SQL » SQL & PL/SQL » query help
query help [message #200355] Mon, 30 October 2006 08:28 Go to next message
Pras80
Messages: 2
Registered: October 2006
Junior Member
Hi,

I have a table whose schema is as follows:

Owner, OwnedCarModel , YearOfManufacture

The data looks like these

1 , Ford , 1990
1 , BMW , 2000
1 , Toyota , 2000
2 , Ford , 2000
2 , Toyota , 1990
3 , Toyota , 1996

Assume that (Owner, OwnedCarModel) together forms a key.

Now, I need to answer the following query: Given a set of models and a year get me all owners who have ALL the car models specified in the query and whose YearOfManufacture is greater than the given year.
As an example, for query :({Ford, BMW} , 1980) the query should return
1 , Ford , 1990
1, BMW , 2000

and for query :({Toyota} , 1995) the query should return
1 , Toyota , 2000
3 , Toyota , 1996

Thanks for all your help;
-Prasanna
Re: query help [message #200356 is a reply to message #200355] Mon, 30 October 2006 08:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Try this if i understand right.
As an example, for query :({Ford, BMW} , 1980) the query should return
select * 
from table_name
where ownedcarmodel in ( 'FORD','BMW')
AND 
yearofmanufacture > '1980';


and for query :({Toyota} , 1995) the query should return

select * 
from table_name
where ownedcarmodel in ( TOYOTA')
AND 
yearofmanufacture > '1995';


Note: Not tested.
Re: query help [message #200358 is a reply to message #200355] Mon, 30 October 2006 08:49 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

Prasanna:
all owners who have ALL the car models specified in the query and whose YearOfManufacture is greater than the given year.



Prasanna,
I think you need to give the number of models also.

select * from table_name
where owner in (select owner 
                  from table_name
                 where ownedcarmodel in ( 'FORD','BMW')
                   AND yearofmanufacture > '1980'
                 GROUP BY owner
                HAVING count(0) = 2); -- I think this is also requied too.


By
Vamsi
Re: query help [message #200435 is a reply to message #200358] Mon, 30 October 2006 22:26 Go to previous messageGo to next message
Pras80
Messages: 2
Registered: October 2006
Junior Member
Thanks both. user52 your query doesnt work as it retruns owners who have either Ford or BMW.
Quote:
vamsi:
select * from table_name
where owner in (select owner
from table_name
where ownedcarmodel in ( 'FORD','BMW')
AND yearofmanufacture > '1980'
GROUP BY owner
HAVING count(0) = 2); -- I think this is also requied too.




vamsi, the above query returned proper results. But it takes a lot of time about (1 minute for 4000 records). Assuming that these are the type of queries I will be firing, is there a better query or indeed better way to design the db schema itself?

Thanks
Re: query help [message #200559 is a reply to message #200435] Tue, 31 October 2006 06:24 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I'd be curious how the following performs for you:

select owner, ownedcarmodel, yearofmanufacture from (
	select a.*, count(*) over (partition by owner order by owner) rn
	from test_car a
	where ownedcarmodel in ('Ford', 'BMW')
	and yearofmanufacture > 1980)
where rn = 2;
Previous Topic: retrieve records for current month
Next Topic: REF CURSOR problem
Goto Forum:
  


Current Time: Sat Dec 03 14:18:09 CST 2016

Total time taken to generate the page: 0.10294 seconds