SQL 如何调用内部联接以获取带有列表的名称

Question

我是MySQL的新手，我一直在尝试如何使用内部联接来获取发布列表的所有用户的名称时遇到问题。任何帮助或提示将不胜感激！有好的一天！该表通过外键链接在一起。

所需的结果将如下所示：

userName, itemName, itemDescription, price

我的SQL的代码如下。

CREATE DATABASE  IF NOT EXISTS `snapsell`;
USE `snapsell`;
CREATE TABLE USER_LIST(
id INT(6)  AUTO_INCREMENT unique not null,
userName VARCHAR(50) NOT NULL unique,
email varchar(100) NOT NULL,
registeredDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
primary key(id)
);
insert into user_list(userName,email) values 
('John', '[e-mail]'),
('Tom', '[e-mail]'),
('Javier', '[e-mail]'),
('Deft', '[e-mail]');

create table listing_list(
id INT(6)  AUTO_INCREMENT PRIMARY KEY,
itemName VARCHAR(50) NOT NULL,
itemDescription VARCHAR(254) NOT NULL,
price DECIMAL(4,2) NOT NULL,
fk_poster_id int not null ,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
unique(id),
constraint fk_poster_id
foreign key(fk_poster_id)
references USER_LIST(id)
on delete cascade
);
insert into listing_list (itemName,itemDescription,price,fk_poster_id) values 
('Pen',' It is a writing instrument used to apply ink to a surface, usually paper, for writing or drawing','1.50','2'),
('Pencil',' Dark blue pencil, used to draw on a surface, usually paper, for writing or drawing','0.50','1'),
('Paper','A stack of 50 foolscap paper.','2','1');

create table offer_list(
id int(6)  auto_increment Primary key unique,
offer int not null,
fk_listing_id int not null ,
fk_offeror_id int not null ,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
constraint `fk_listing_id`
foreign key (`fk_listing_id`)
references listing_list(id)
on delete cascade,
constraint `fk_offeror_id`
foreign key (`fk_offeror_id`)
references user_list(id)
on delete cascade
);
insert into offer_list(offer,fk_listing_id,fk_offeror_id) values 
(2,1,3),
(3,1,4),
(0.5,2,2),
(9,1,3),
(1.50,3,5);

Alter table user_list add column password varchar(255) not null;
select* from listing_list;

Answer 1

我只想要发布至少一个报价的用户的名称，您可以使用exists：

select u.*
from user_list u
where exists (select 1 from offer_list o where o.fk_offeror_id = u.id)

如果您希望每个用户/产品/服务元组有一行和更多列，则可以按如下方式join：

select u.userName, l.itemName, l.itemDescription, l.price
from user_list u
inner join offer_list o on o.fk_offeror_id = u.id
inner join listing_list l on l.id = o.fk_listing_id

Answer 2

您不需要表offer_list来满足此要求。
加入user_listlisting_list：

select u.userName, l.itemName, l.itemDescription, l.price
from user_list u inner join listing_list l
on l.fk_poster_id = u.id
order by u.userName, l.itemName

请参阅演示。
结果：

| userName | itemName | itemDescription                                                                                   | price |
| -------- | -------- | ------------------------------------------------------------------------------------------------- | ----- |
| John     | Paper    | A stack of 50 foolscap paper.                                                                     | 2     |
| John     | Pencil   | Dark blue pencil, used to draw on a surface, usually paper, for writing or drawing                | 0.5   |
| Tom      | Pen      | It is a writing instrument used to apply ink to a surface, usually paper, for writing or drawing  | 1.5   |