MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.18


CREATE TABLE user_list
(
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  userName VARCHAR(50) NOT NULL,
  email varchar(100) NOT NULL,
  registeredDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);


create table listing_list(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
itemName VARCHAR(50) NOT NULL,
itemDescription VARCHAR(254) NOT NULL,
price DECIMAL(4,2) NOT NULL,
fk_poster_id int references USER_LIST(id),
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

create table offer_list(
id int(6) Unsigned auto_increment Primary key,
offer int,
fk_listing_id int references listing_list(id),
fk_offeror_id int references user_list(id),
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);



insert into user_list (id, userName, email) values (10, 'John','johnnyboi@123.com');
insert into user_list (id, userName, email) values (20, 'Tom','Tommyboi@123.com');
insert into listing_list (itemName,itemDescription, price, fk_poster_id) 
values ( 'Pen', 'A long delicate pen.',' 1.50 ', 10);
insert into listing_list (itemName,itemDescription, price, fk_poster_id) 
values ( 'Pencil', 'A long delicate pencil.',' 0.50 ', 20);
insert into offer_list (offer,fk_listing_id,fk_offeror_id) 
values ('200','1', 10);
insert into offer_list (offer,fk_listing_id,fk_offeror_id) 
values ('200','2', 20);
SELECT * FROM user_list;
SELECT * FROM listing_list;
SELECT * FROM offer_list;


id
userName
email
registeredDate


10
John
johnnyboi@123.com
2019-12-23 11:21:10

20
Tom
Tommyboi@123.com
2019-12-23 11:21:10




id
itemName
itemDescription
price
fk_poster_id
created_at


1
Pen
A long delicate pen.
1.50
10
2019-12-23 11:21:10

2
Pencil
A long delicate pencil.
0.50
20
2019-12-23 11:21:10




id
offer
fk_listing_id
fk_offeror_id
created_at


1
200
1
10
2019-12-23 11:21:10

2
200
2
20
2019-12-23 11:21:10


select 
  u.id,
  u.username,
  u.email,
  l.id, 
  l.itemName, 
  l.itemDescription,l.price,
  l.fk_poster_id,
  l.created_at,
  o.offer,
  o.fk_listing_id
FROM user_list u
JOIN listing_list l ON u.id = l.fk_poster_id
JOIN offer_list o ON u.id = o.fk_offeror_id;
  


id
username
email
id
itemName
itemDescription
price
fk_poster_id
created_at
offer
fk_listing_id


10
John
johnnyboi@123.com
1
Pen
A long delicate pen.
1.50
10
2019-12-23 11:21:10
200
1

20
Tom
Tommyboi@123.com
2
Pencil
A long delicate pencil.
0.50
20
2019-12-23 11:21:10
200
2

id	userName	email	registeredDate
10	John	johnnyboi@123.com	2019-12-23 11:21:10
20	Tom	Tommyboi@123.com	2019-12-23 11:21:10

id	itemName	itemDescription	price	fk_poster_id	created_at
1	Pen	A long delicate pen.	1.50	10	2019-12-23 11:21:10
2	Pencil	A long delicate pencil.	0.50	20	2019-12-23 11:21:10