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
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
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