Advanced Access Query

Question

I have two tables. One 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 Vehicles (from the Vehicles table) that satisfy 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 fields:

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?

Answer 1

But you already have several ... AND ... statements. So I think that a good way to do it is:

SELECT Cars.* FROM Cars, Customer WHERE 
Customer.ID = 1 AND
Cars.Whatever >= Customer.Whatever >=  AND
...

I, personally, would do it that way because it's easy and understandable. Also, for about 8 years of marginal database experience, I never bothered to learn anything about joins (ashamed). And, BTW, this is not such an advanced query :P

Answer 2

You may be able to get what you are after by using a simple query like this:

SELECT Customer.Id, Vehicle.Id FROM Customer, Vehicle 
WHERE Vehicle.criteria_1 >= Customer.Criteria_1 AND... AND Customer.Id = 3

That should give you a list of Vehicle.Id (or whatever else you select form Vehicle) for a specific customer.

BTW, how is the query going to be created? Ad-hoc in code? Stored proc?