开发者

MYSQL Inner Join if statement

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`开发者_JAVA百科, 
  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 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

Not unique table/alias: 'staff'

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

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.

Thanks.


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


Doesn't this works?

INNER JOIN `staff` ON `user`.userID = staff.userID AND user.level = 2
INNER JOIN `customer` ON user.userID = customer.userID AND user.level = 1 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜