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
精彩评论