如何将table1的userid替换为table2的username

Question

TABLE-1

INSERT INTO `r_store`.`tb_user`(`id`,`username`,`password`,`role`,`actions`)
VALUES(1,'SSP','123','ADMIN',1),(2,'RRP','123','ADMIN',1);

从r_store.tb_user中选择*；

'1','SSP','123','ADMIN','1'
'2','RRP','123','ADMIN','1'

表2

INSERT INTO `r_store`.`tb_main_product`(`shortname`,`mainproduct`,`tb_user_id`)
VALUES('RR','RAJ',1),('LK','LAKS',2),('DY','DIYA',5),('DY','DIYA',3);

从r_store.tb_main_product中选择*；

'21','RR','RAJ','1'
'22','LK','LAKS','2'
'23','DY','DIYA','1'
'24','DY','DIYA','2'
'25','RR','RAJ','1'
'26','LK','LAKS','2'
'27','DY','DIYA','1'
'28','DY','DIYA','2'

我需要像一样

'21','RR','RAJ','SSP'
'22','LK','LAKS','RRP'
'23','DY','DIYA','SSP'
'24','DY','DIYA','RRP'

我试过这些查询

SELECT shortname,mainproduct,username FROM tb_main_product left join  tb_user USING (id);
SELECT shortname,mainproduct,username FROM tb_main_product join  tb_user USING (id);

它不起作用，请帮助我

Answer 1

您必须组合正确的列

SELECT 
shortname, mainproduct, username
FROM
tb_main_product tmp
LEFT JOIN
tb_user tu ON tu.id = tmp.tb_user_id