Straight join on a left join?

[bubblenut]

Is it possible to do a straight join on a left join with MySQL?
If it's not clear what I mean here is an example. I have three tables a, b and c I can do the following

SELECT * FROM a LEFT JOIN b ON (a.id=b.a_id) LEFT JOIN c ON (b.c_id=c.id)

I cannot move b to the main FROM cause because in the real scenareo the query is much more complex. So, it works like this but I do not need the left join for table c as it can quite happily be straight joined onto b but only in terms of b's left join to a ie. it must be within the left join from b to a somehow :eek: I tried the following but things went more than a little hectic (it didn't restrict table c at all so I got quite a few rows out (about 180,000)

SELECT * FROM a, c LEFT JOIN b ON (a.id=b.a_id, b.c_id=c.id)

Thanks in advance
Bubble

[tekky]

I dunno about joins, but looking at what you are doing.... it would seem just

SELECT * FROM a,b,c WHERE a.id=b.a_id AND b.c_id=c.id

would do what you are trying...

[AstroTeg]

SELECT * FROM a, c LEFT JOIN b ON a.id = b.a_id WHERE b.c_id = c.id

That might work. Its similar to tekky's, but tekky's doesn't take into account the 1-to-many join on table b to table a.

[bubblenut]

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

[AstroTeg]

Have you tried just doing "deck_manufacturer.man_id=decks.man_id" in the where clause and just include deck_manufacturer in the from clause? Or does this give you an error?

I'm thinking a view is the way to go here. Setup the view to associate the decks with the manufactures. Then you could query off of that a bit easier (and might help with performance). But MySQL isn't able to do this... Hmm.

Well, maybe tackle it from the other direction - what are you really trying to do? You posted a working query. What's the issue you are having?

[tekky]

Originally posted by AstroTeg
Have you tried just doing "deck_manufacturer.man_id=decks.man_id" in the where clause and just include deck_manufacturer in the from clause? Or does this give you an error?

I'm thinking a view is the way to go here. Setup the view to associate the decks with the manufactures. Then you could query off of that a bit easier (and might help with performance). But MySQL isn't able to do this... Hmm.

Well, maybe tackle it from the other direction - what are you really trying to do? You posted a working query. What's the issue you are having?

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.

because of that....

[mtimdog]

It it's a problem, then break it into 2 queries. It's too bad you can't left join on more than one table in mysql (your straight join on the left join). That seems pretty useful.

Hmm....one could always wait until MySql upgrades it's featureset
🙂 🙂