Advanced Query

[Cined]

Hi,

Hopefully I can explain this well enough...

So I have two tables. One which contains Potential Customer information along with their Vehicle requirements (Vehicle Type, Vehicle Colour) etc...



The other table contains a list of the Vehicles. This includes data such as NumberOfSeats, Max Speed, Price etc...


I need a query that will list of Vehicles (from the Vehicles table) that satisfy's the Potential Customers requirements (Vehicle Type) etc.

There's a few things I'd like to avoid in the query. I want to list these by ONLY specifying the Potential Customer's ID (Cust ID). I.E I don't want to have to do something like WHERE `Cust ID` = 1 AND ... AND ... AND ...


I thought about this and concluded that a JOIN or UNION is most likely needed to be used. But when I was trying to put a JOIN statement together, I found that I'd have to list loads of JOIN ON feilds:

SELECT * 
FROM [Potential Customer] INNER JOIN [Vehicles] AS Matches
	ON Matches.`Number of Seats` >= [Potential Customer].`Min Seats` AND
	ON Matches.`Color` >= [Potential Customer].`Preferred Color` =  AND 
	...	 

WHERE [Potential Customer].`Cust No` = 3

Is there a better way to do this?


Thanks.

[Cined]

Really, no one?