如何有条件地加入不同的表？

I am using MySQL.

我正在使用MySQL。

I have three tables named ratings, users and one master table master_entityType.

我有三个名为rating,users和一个master table master_entityType的表。

Depending on the values from entityTable column of the master_entityType table, I have to join with another table. If the values from master_entityType is "Blogs", I have to join with blogs table. If the values from master_entityType is "items", I have to join with items table.

根据master_entityType表的entityTable列的值,我必须与另一个表连接。如果master_entityType中的值是“博客”,我必须加入博客表。如果master_entityType中的值是“items”,我必须使用items表连接。

SELECT * FROM ratings AS r
    LEFT JOIN users AS u ON u.userID = r.userID
    LEFT JOIN master_entityType AS ms ON ms.entityTypeID = r.entityTypeID   
    CASE ms.entityTable
        WHEN 'Blogs' THEN INNER JOIN blogs AS b ON b.blogID = r.entityID
    END 
WHERE r.entityTypeID = '10' AND r.entityID = '1' AND r.userID = '1'

While using the above query I am getting error, please suggest some step to get that query to work.

使用上面的查询我收到错误,请建议一些步骤,以使该查询工作。

Structure of the table are as follows,

表的结构如下,

In users table,

在用户表中,

UserID  userName isActive 
 1       Dinesh    1
 2       Kumar     1

In ratings table,

在评级表中,

ratingID   entityID  entityTypeID   userID  rating
  1           1          1            1       5
  2           4          2            1       4

In master_entityType table,

在master_entityType表中,

entityTypeID  entityTable  entityTypeName  entityTypeDescription  active
      1          blogs           Blogs             Null              1
      2          items           Items             Null              1

In Items table,

在Items表中,

ItemID   name    collection     active
   4    pencil       12            1
   5     pen         06            1

In blogs table,

在博客表中,

blogID   name     active
   1    socail      1
   2    private     1

1 个解决方案

#1

Your design is strange, so performance is likely to be poor.

你的设计很奇怪,所以性能可能很差。

UNION ALL two tables together and join with the result. Something like this. If MySQL has views, then create view that unions Items and Blogs table and use the view in other queries. It makes the queries easier to read, understand and maintain.

UNION ALL两个表一起加入结果。像这样的东西。如果MySQL有视图,则创建联合项目和博客表的视图,并在其他查询中使用该视图。它使查询更易于阅读,理解和维护。

Here is SQL Fiddle. I adjusted the WHERE condition in the fiddle, because sample data doesn't have any rows with entityTypeID = 10.

这是SQL小提琴。我调整了小提琴中的WHERE条件,因为样本数据没有entityTypeID = 10的任何行。

SELECT * 
FROM 
    ratings AS r
    LEFT JOIN users AS u ON u.userID = r.userID
    LEFT JOIN master_entityType AS ms ON ms.entityTypeID = r.entityTypeID
    INNER JOIN
    (
        SELECT 
            ItemID AS EntityID
            ,'Items' AS EntityTypeName
            ,name
            ,active
        FROM items

        UNION ALL

        SELECT 
            BlogID AS EntityID
            ,'Blogs' AS EntityTypeName
            ,name
            ,active
        FROM blogs
    ) AS Entities ON 
        Entities.EntityTypeName = ms.entityTypeName
        AND Entities.EntityID = r.entityID
WHERE r.entityTypeID = '10' AND r.entityID = '1' AND r.userID = '1'

#1