MYSQL内部连接if语句 [英] MYSQL Inner Join if statement

问题描述

我正在尝试连接3个表，但使用IF语句只运行某个查询

i'm trying to join 3 tables together but use an IF statment to only run a certain query

我有一个用户表，客户和员工表。下面的第一部分有效：

I have a user table, customer and staff table. The first part below works:

SELECT user.`userID`, `username`,  `user`.email, `registeredBy`, `registeredDate`, 
  CONCAT(staff.firstName, ' ' ,staff.lastName) AS staffName FROM `user` 
   INNER JOIN `level` ON `user`.levelID = `level`.levelID 
    INNER JOIN `staff` ON `user`.registeredBy = `staff`.userID 

I需要查看谁注册了显示罚款的用户，但是如果用户级别为2则需要查看员工姓名或者如果用户级别为1则需要查看客户名称

I need to view who registered the user which shows fine, but then I need to view either the staff name if the user level is 2 or the customer name if the user level is 1

INNER JOIN `staff` ON `user`.userID = staff.userID IF (user.level = 2)
INNER JOIN `customer` ON user.userID = customer.userID IF(user.level = 1)   

添加以上内容会产生错误

Add the above creates the error

不唯一的表/别名：'staff'

Not unique table/alias: 'staff'

如果我删除工作人员的内部联接我然后得到错误

If I remove the inner join for staff I then get the error

您的SQL语法有错误;
检查对应于
的手册，你的MySQL服务器版本是
正确的语法，在第6行的
'IF（user.level = 2）'附近使用

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(user.level = 2)' at line 6

我只需要根据级别显示客户名称或员工姓名，但我无法理解我如何两次加入员工表。一旦获得注册用户的员工，然后查看员工姓名（或客户）。

I need to only show the customer name or the staff name depending on the level, but I cannot understand how as I am joing the staff table twice. Once to get the memeber of staff who registered the user and then to view the staff name(or customer).

为了简化操作，我要显示的页面示例如下：

To help make things easier an example of the page I am displaying is:

用户名|名字|帐户类型|
注册

username | name | account type | registered by

希望我没有理解它太混乱。

Hope I haven't made it too confusing to understand.

谢谢。

推荐答案

Select user.userID
    , username
    , user.email
    , registeredBy, registeredDate
    , Case 
        When User.Level = 2 Then Concat(Level2Staff.firstName, ' ' , Level2Staff.lastName) 
        When User.Level = 1 Then Concat(customer.firstName, ' ' , customer.lastName) 
        End
        AS staffName 
FROM user
    Inner Join level
        On user.levelID = level.levelID 
    Inner Join staff
        On user.registeredBy = staff.UserID
    Left Join customer
        On customer.userID = user.UserID
            And user.level = 1
    Left Join staff As Level2Staff
        On user.userID = Level2Staff.UserID

这篇关于MYSQL内部连接if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！