think of LEFT JOIN in Query Statement while DB design

Hi DB Experts,

Under enormous pressure of a deadline I gathered all my brains to develope the following database sketch.

I know it's far from perfect, but it's hopefully descent enough so you can take a look at it.

Table1 "Main"
Has itemID, Season, Price, Class, Type.

Table2 "Zones"
Has Zone, itemID.
Because some suppliers have bundled multiple items under one zone with the same class, price, etc. I came up with the idea to create an itemID for that zone where that itemID will match the ID of all the items in that zone.

Table3 "Items"
The "Main" table has items identified by codes, where table3 has all the item codes and its' definitions.

Table4 "Suppliers"
The "main" Table has a 2 char. supplierID with each item, so table4 is needed to identify the suppliers, and I also added the link to each suppliers' .htm file of rules

Table5 "Seasons"
supplier, season, start, end.
The "main" table has also a season column with codes where every item is priced differently for different seasons. So a season needs 2 coloumns "startDate" and "endDate", needless to say that every supplier besides of having differen season periods, also have different number of periods under the same season, so they couldn't be placed in table4.

For example, supplier1 has under the season "basic" 2 date periods (4/01/01-5/12/01 and 6/11/01-7/08/01) where supplier2 has 4 date periods for "basic". Supplier3 has diffferent date periods for different items... it's just endless, so I put them all into one table.

So, there we go, I always knew myself as somebody who lacks a good understanding of INNER JOINS or LEFT JOIN or RIGHT JOIN as well as UNION. I think that this is an actual great oppurtunity to see those methods go to work "in real life" but I need an expert like you to show me the first baby steps...

If you want an example SQL statement it's probably going to be very poorly written, but hopefully you'll get where I'm trying to go:

SELECT * from main, zones, items, supplier, seasons
WHERE main.itemID = #form.itemID#
<cfif isdefined(form.itemClass)>
AND main.class = #form.class# </cfif>
<cfif isdefined(form.supplier)>
AND main.supplier = #form.supplier#
AND main.supplier = suppliers.supplier </cfif>
<cfif isdefined(form.season)>
AND main.seasons = seasons.season
AND seasons.start <= #form.date# </cfif>

ORDER BY main.price

------------------

How does it look so far? A little bit CF mixed in.. But I know I can do much better with LEFT JOINS or UNIONS etc., but I leave this for you to show me...