MySQL conditional table name in JOIN

Question

I'm working on intellecual property management system, where I have 2 tables in database - trademark and design.

Then I have such thing as opposition. This means, if someone else has trademark or design, that looks like our's clients one, manager can create new opposition.

For example, I have 3 tables:

trademark:

id 
name

design:

id
name

oppostion:

id
name
object_id
object_table

I don't know, to what table opposition is related, but I should have a possibility to make such kind of query:

SELECT id, name, opposition_object.name FROM opposition
LEFT JOIN (trademark|design) as opposition_object on opposition.object_id =   (trademark|design).id

First, I thought about storing table name as object_table column of opposition table, but then I realised I don't know if it will be possible to query table name and, first of all, it's defenitely a bad design.

But I got strucked here and nothing comes to my mind. So does anyone have any ideas how to handle it?

score 3 · Accepted Answer · answered Apr 26 '12 at 10:53

3

Maybe something like this:

SELECT 
    id, 
    name, 
    COALESCE(trademark.name,design.name) AS object_name
FROM 
    opposition
    LEFT JOIN trademark 
        on opposition.object_id =   trademark.id
        AND trademark.object_table ='trademark'
    LEFT JOIN design
        on opposition.object_id =   design.id
        AND design.object_table ='design'

answered Apr 26 '12 at 10:53

Arion

31,011
10
70
88

@KuH also, compare the performance with the query from my answer. I am not entirely sure, but I feel like my query should be faster, returning the same result. – Dmytro Shevchenko Apr 26 '12 at 11:16
1

@KuH: No problem. Remember to up vote the answer you think are good. That gives us a warm fuzzy feeling :P. – Arion Apr 26 '12 at 11:17
@Shedal : Who can a `UNION` be faster then 2 `left join`s? – Arion Apr 26 '12 at 11:18
@Shedal: The thing is that I use Zend Framework for my project and it will be not convinient to use union in it. But anyway, thanks for a reply! – Serge Kuharev Apr 26 '12 at 11:26

score 0 · Answer 2 · answered Apr 26 '12 at 10:50

SELECT opposition.id, opposition.name, trademark.name AS object_name
FROM opposition
LEFT JOIN trademark
  ON opposition.object_id = trademark.id
WHERE opposition.object_table = 'trademark'

UNION

SELECT opposition.id, opposition.name, design.name AS object_name
FROM opposition
LEFT JOIN design
  ON opposition.object_id = design.id
WHERE opposition.object_table = 'design'

This issues two queries: one joining the trademark table and one joining design. Then the results are unified into one result set using UNION.

MySQL conditional table name in JOIN

2 Answers2

Linked