I have four tables: Charts, Colours, MaterialTypes, Materials.
There is a one-to-many relationship between the Charts and Colours (i.e. each Chart can have 0 or more Colours). There is a one-to-many relationship between the MaterialTypes and Materials (i.e. each MaterialType can have 0 or more Materials). There is a one-to-one relationship between the Colours and Materials (each Colour record will have one Material record).
The fields in the tables look like this:
Charts: ChartNum INTEGER PRIMARY KEY Title VARCHAR (60)
Colours: ChartNum INTEGER NOT NULL MaterialType VARCHAR (10) NOT NULL MaterialCode VARCHAR (10) NOT NULL Quantity NUMERIC DEFAULT 0
MaterialTypes: Code VARCHAR (10) NOT NULL Description VARCHAR (50) NOT NULL
Materials: MaterialType VARCHAR (10) NOT NULL MaterialCode VARCHAR (10) NOT NULL Description VARCHAR (50) Quantity INTEGER DEFAULT 0
Each Colour has a Quantity field to record how many of each colour is required. And the Materials also has a Quantity field to record how many of each material I have on hand.
So, for example, to make a particular chart I have to look at all of the colours that the chart uses and see how many of each colour I need. Then I need to look at the materials to see if I have enough of those colours on hand. An example:
Chart number 1 requires the following colours: MaterialType = Acrylic MaterialCode = Blue Quantity = 1 MaterialType = Acrylic MaterialCode = Red Quantity = 2 MaterialType = Enamel MaterialCode = Blue Quantity = 1
If I look up the colours in the Materials table I might find the following: MaterialType = Acrylic MaterialCode = Blue Quantity = 5 MaterialType = Acrylic MaterialCode = Red Quantity = 1 MaterialType = Enamel MaterialCode = Blue Quantity = 25
With this example I can't make Chart 1 because I don't have enough Acrylic Red (I need at lease 2 and there's only 1 on hand). But I've got more than enough of the other colours.
What I need is an SQL statement that will give me the ChartNum's of all the Charts that I can make with my current stock of Materials. I suspect I'm going to need some sort of inner join that can compare the two Quantity fields and only include the ChartNum for those records where the Materials.Quantity is >= Colours.Quantity for all Colours for a particular Chart. Is that possible?
