Need help to get as "appointed" and as "appointedby"

@r937 @DaveMaxwell

Hi, can ask some help

I have this table


CREATE TABLE `tbl_user` (

`fname` VARCHAR(20) NOT NULL,
`mname` VARCHAR(20) NOT NULL,
`email` VARCHAR(100) NOT NULL,




CREATE TABLE `tbl_appointed` (
    `appointed` BIGINT(20) NOT NULL,
    `appointedby` BIGINT(20) NOT NULL,
    PRIMARY KEY (`id`)

and this is the value in my tbl_appointed

id     appointed      appointedby
1      5              10
2      3               30

appointed and appointedby are the id of tbl_user

How can I populate like this

Appointed             AppointedBy

John Mek             Charles Lee
Shane Orlen          David Lambert

here is my sql query but I cannot get the appointedby

select, concat(usr.fname, ' ' ,usr.lname) as 'Appointed', concat(usr.fname, ' ' ,usr.lname) as 'AppointedBy'
  FROM tbl_user  as usr
  inner join tbl_appointed as appntd
  on = appntd.appointed
 and = appntd.appointedby

Thank you in advance.

I’m not that good at SQL but looks like you have to select from tbl_appointed and join users table twice
Something like that:

    concat(usr1.fname, ' ' ,usr1.lname) as 'Appointed', 
    concat(usr2.fname, ' ' ,usr2.lname) as 'AppointedBy'
FROM tbl_appointed as appntd
JOIN tbl_user as usr1 ON = appntd.appointed
JOIN tbl_user as usr2 ON = appntd.appointedby
1 Like

Thank you it’s working…hmmm I tried that join before I posted this question. I just have some mistake not putting usr1 and usr2.

Thank you again.

FYI unless you plan to have over 2 BILLION users, you should use INTEGER and not BIGINT

also, quick question, can a user be appointed by multiple other users, or just one?

Thank you for correcting my datatype,

just one

can you also show how to have appointed mulitple other users ?

Thank you in advance.

in that case, you don’t need a separate table, just add appointedby to users – this means each user is appointed by only one user, but one user can appoint multiple users

for a true many-to-many relationship (one user can appoint multiple users, and one user can be appointed by multiple users), you would keep your separate table

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.