JOIN 中的 MySQL 条件表名 [英] MySQL conditional table name in JOIN

问题描述

我正在研究知识产权管理系统，我的数据库中有 2 个表 - 商标和设计.

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.

例如，我有 3 个表:

For example, I have 3 tables:

商标:

id 
name

设计:

id
name

反对:

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

首先，我想将表名存储为对立表的 object_table 列，但后来我意识到我不知道是否可以查询表名，首先，这绝对是一个糟糕的设计.

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.

但是我在这里被击中了，什么也没想.那么有没有人有任何想法如何处理它?<​​/p>

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

推荐答案

也许是这样的:

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'

这篇关于JOIN 中的 MySQL 条件表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！