mysql if join_MYSQL内部连接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 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