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

Databases
1

@r937 @DaveMaxwell

Hi, can ask some help

I have this table

tbl_user

CREATE TABLE `tbl_user` (

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

PRIMARY KEY (`id`),

)

tbl_appointed

CREATE TABLE `tbl_appointed` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `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 usr.id, 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 usr.id = appntd.appointed
 and usr.id = appntd.appointedby

Thank you in advance.

2

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:

SELECT
    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 usr1.id = appntd.appointed
JOIN tbl_user as usr2 ON usr2.id = appntd.appointedby
1 Like
3

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.

4

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?

5

Thank you for correcting my datatype,

just one

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

Thank you in advance.

6

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

7

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