Creating a left outer join in MySQL with right-hand constraints

Question

As per http://stackoverflow.com/questions/3264227/relations-with-multiple-keys-in-doctrine-1-2, I have two tables which (as I can't get it to work in Doctrine) I'm trying to join as a MySQL view:

part:

• part_id
• product_id
• part_type_id
• part_short_code
• ...

part_translation:

• part_type_id
• part_short_code
• language_id
• internationalised_name
• ...

on the constraint that part_type_id and part_short_code must match across the tables.

This is complicated by the facts that:

• The right-hand table (part_translation) may not have a corresponding row (hence the left join)
• The right-hand table is only valid to join when part_short_code != '' and language_id = 1

I have a working query:

SELECT * from part LEFT OUTER JOIN (SELECT * FROM part_translation WHERE language_id=1 AND part_short_code != '') as part_data_uk USING (part_type_id, part_short_code)

but a) it's slow (probably due to the subquery) and b) I can't turn it into a view because I get the error "#1349 - View's SELECT contains a subquery in the FROM clause"

I can't work out how else add the constraints to the right-hand table without causing the join to act as an inner join and skill all rows where the right-hand side is null.

Can anyone tell me how to optimise / improve this query in such a way as to get rid of the subquery and make it usable in a view?

Using MySQL 5.0.45, FWIW.

Thanks, Richard

Answer 1

You can put pretty much anything in th ON part. Will not be a pretty sight but it can work:

SELECT * FROM `part` `p` LEFT JOIN `part_translation` `t` ON (
    `p`.`part_type_id` = `t`.`part_type_id`
    AND `p`.`part_short_code` = `t`.`part_short_code`
    AND `t`.`part_short_code` != ""
    AND `t`.`language_id` = 1
)