Hi!

This is an extract of my current table structure.

table: order
Code:
orderid | dimensionclass | amount | deliverydate
=======================================================
      1 |              1 |  34.50 | 2002-18-06 15:30:00
      2 |              3 |  66.50 | 2002-19-06 15:30:00
table: vehicle
Code:
vehicleid | vehicletype | ownerid | dimensionclass | maxweight
==============================================================
        1 |           1 |       1 |              2 |     10.00
        2 |           4 |       1 |              2 |    500.00
table: dimension
Code:
dimensionclass | length | width | height
========================================
             1 | 100.00 | 75.00 |  40.00 
             2 | 150.00 | 90.00 |  60.00
table vehicle_type
Code:
type_id | description
=====================
1       | bicycle
2       | small car
3       | medium car
4       | truck
table: delivery_times
Code:
dtid | start | end
==================
1    | 0     |   4
2    | 4     |   8
3    | 8     |  12
4    | 12    |  16
5    | 16    |  20
6    | 20    |   0
table: vehicle_delivery_times

Code:
vehicleid | dtid
================
        1 |    2
        1 |    5
        1 |    6
        2 |    4

I have left out some fields like the "order address" since I thought
they were not needed for this problem.
The dimension for an order or a vehicle is calculated based
on the data entered in a form.
There is a n:m relationship between vehicles and their delivertimes.

I hope these information are enough to understand what I want to do.
I want to select for one or all vehicle of an owner those orders
which meet these requirements:

  • dimensionclass of the order <= weightclass of the vehicle
  • amount of the order <= maxamount of the owners vehicle


If these requirements are meet, finally the delivery times of the vehicle
have to match the deliverydate of the order.

A query should return something like this:

Code:
vehicleid | orderid
===================
2         | 1
2         | 2
Currently, I am using PHP to solve this problem with several queries and
an array comparison of the results.

Is it possible to solve this problem with one query in mysql?
If not, I would still be interested in a solution.

Thanks in advance.

Regards, Christian.

BTW: Is this a good database design?