Vehicle dealership SQL search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Vehicle dealership SQL search

Author

Topic

mfazio
Starting Member

17 Posts

Posted - 2005-12-22 : 09:11:18

Hi guys, id really appreciate some help on this one, its been giving me alot of troubles...

Ive been developing a web site for a car dealership, i have now come to the stage where i must format a search statement to retrieve vehicle records. I must be able to filter these records based on the following paramaters.

@ModelID int,
@MakeID int,
@PriceMin money,
@PriceMax money,
@YearMin int,
@YearMax int

Now... I have a Makes table, Models table and Vehicles table with the following cardinality.

Makes 1-------m Models 1-------m Vehicles

MakeID PK ModelID PK StockNumber PK
Make MakeID FK ModelID FK
Model Colour
Year
Etc
Etc

Here is where it becomes tricky....

If the MakeID is supplied as 0... it is to retrive all makes, otherwise only the makes corresponding to the MakeID

The same is for Models... 0 for all, otherwise a specific Model...

Then it must filter the records given the year range...

Then it must filter the records given the price range....

After all this i must incorperate custom paging.... this i can handle myself....

If anyone can help me on a way to complete these tasks i would really appreciate it... thank you...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-22 : 09:37:38

where (@MakeID = 0 or @MakeID = MakeID)
and (@ModelID = 0 or @ModelID = ModelID)
and etc...

mfazio
Starting Member

17 Posts

Posted - 2005-12-22 : 09:48:00

quote:
Originally posted by blindman

where (@MakeID = 0 or @MakeID = MakeID)
and (@ModelID = 0 or @ModelID = ModelID)
and etc...

this is not quite my requirements.. if 0 is supplied all records are to be supplied, otherwise just the selected vehicle....

and if then would be nice... but im not sure how to integrate this all in one statement..

ie..

IF (@ModelID >0) SELECT * FROM Vehicles WHERE ModelID = @ModelID
ELSE SELECT * FROM Vehicles

and then on to the rest of the filtering....

jhermiz

3564 Posts

Posted - 2005-12-22 : 09:50:19

What he posted was exactly what you need.

Did you try it out ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

mfazio
Starting Member

17 Posts

Posted - 2005-12-22 : 10:00:39

i do apologise... thank you both very much...

solution worked a treat, i was way to quick to discount it