Well, the real situatuation is actually a lot more complex. tekky's solution fails because b must be left joined, AstroTeg's solution fails because there can be more than one table like b so I cannot have b or c mentioned in the overall where condition.
I cannot do straight joins accross from table a to b . I have redone the example below in a little more detail.
products
product_code
product_type
name
description
product_types
product_type
name
description
decks
deck_id
product_code
release_date
deck_manufacturer
man_id
deck_id
name
description
posters
poster_id
product_code
SELECT products.product_code as product_code, products.product_type as product_type, products.name as product_name,
products.description as description, product_types.name as product_type_name, deck.deck_id as deck_id,
deck.release_date as deck_release_date, deck_manufacturer.man_id as deck_man_id,
deck_manufacturer.name as deck_man_name, deck_manufacturer.description as deck_man_desc,
posters.height as poster_height, posters.width as width
FROM products, product_types
LEFT JOIN decks ON (decks.product_code=products.product_code)
LEFT JOIN deck_manufacturer ON (deck_manufacturer.man_id=decks.man_id)
LEFT JOIN posters ON (posters.product_code=products.product_code)
WHERE product_types.product_type=products.product_type && products.product_code='1234'
This is still a very simplified version of what's going on but I think it catches all the points. Before anyone asks, no, I cannot change the design, no question.
Right, I can straight join between products and product_type because there will always be a product type, no problem there.
I have to left join decks and posters because if a product is a deck then it cannot be a poster so if these tables are not left joined then the query would return nothing no matter what. Now a deck always has a manufacturer, and only one manufacturer and this manufacturer can always be found in the deck_manufacturers table, this means that in theory deck_manufacturers could be straight joined onto decks. however, because I have left joined decks onto products and poster's is left joined as well the straight join to deck_manufacturer must take place within the left join between decks and products.
The above query does work but but the left join on deck manufacturer is unnecesarry, this may seem unimportant but the implication is quite big. If I cannot straight join any further down from a table which has been left joined then the way I can manipulate the query is greatly reduced.
Hope this makes a little more sense than the last example
Cheers
Bubble