MariaDB 10.6

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

create table general (buyer_id int, date_sold date);

create table widgets (widget_id int, widget_buyer int);
create table doohickeys (doohickey_id int, doohickey_buyer int);

create table clients (client_id int, buyer_id int);

insert into general values
  (1, '1970-01-01'),
  (2, '2023-03-02');
  
insert into widgets values
  (444, 1),
  (333, 2),
  (222, 2),
  (111, 2);

insert into doohickeys values
  (999, 1),
  (888, 2),
  (777, 2),
  (666, 2);

insert into clients values
  (11, 1),
  (22, 2);

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0


SELECT
  *
FROM
  clients
INNER JOIN
  general
    ON general.buyer_id = clients.buyer_id
INNER JOIN
  widgets
    ON widgets.widget_buyer = general.buyer_id
INNER JOIN
  doohickeys
    ON doohickeys.doohickey_buyer = general.buyer_id
WHERE
  general.date_sold > '2023-03-01';


client_id
buyer_id
buyer_id
date_sold
widget_id
widget_buyer
doohickey_id
doohickey_buyer


22
2
2
2023-03-02
333
2
888
2

22
2
2
2023-03-02
222
2
888
2

22
2
2
2023-03-02
111
2
888
2

22
2
2
2023-03-02
333
2
777
2

22
2
2
2023-03-02
222
2
777
2

22
2
2
2023-03-02
111
2
777
2

22
2
2
2023-03-02
333
2
666
2

22
2
2
2023-03-02
222
2
666
2

22
2
2
2023-03-02
111
2
666
2


WITH
  items (item_type, buyer_id, item_id)
AS
(
  SELECT 'doohickey', doohickey_buyer, doohickey_id
    FROM doohickeys

  UNION ALL

  SELECT 'widget'   , widget_buyer   , widget_id
    FROM widgets
)
SELECT
  *
FROM
  clients
INNER JOIN
  general
    ON general.buyer_id = clients.buyer_id
INNER JOIN
  items
    ON items.buyer_id = general.buyer_id
WHERE
  general.date_sold > '2023-03-01';


client_id
buyer_id
buyer_id
date_sold
item_type
buyer_id
item_id


22
2
2
2023-03-02
widget
2
111

22
2
2
2023-03-02
widget
2
222

22
2
2
2023-03-02
widget
2
333

22
2
2
2023-03-02
doohickey
2
666

22
2
2
2023-03-02
doohickey
2
777

22
2
2
2023-03-02
doohickey
2
888

client_id	buyer_id	buyer_id	date_sold	widget_id	widget_buyer	doohickey_id	doohickey_buyer
22	2	2	2023-03-02	333	2	888	2
22	2	2	2023-03-02	222	2	888	2
22	2	2	2023-03-02	111	2	888	2
22	2	2	2023-03-02	333	2	777	2
22	2	2	2023-03-02	222	2	777	2
22	2	2	2023-03-02	111	2	777	2
22	2	2	2023-03-02	333	2	666	2
22	2	2	2023-03-02	222	2	666	2
22	2	2	2023-03-02	111	2	666	2

client_id	buyer_id	buyer_id	date_sold	item_type	buyer_id	item_id
22	2	2	2023-03-02	widget	2	111
22	2	2	2023-03-02	widget	2	222
22	2	2	2023-03-02	widget	2	333
22	2	2	2023-03-02	doohickey	2	666
22	2	2	2023-03-02	doohickey	2	777
22	2	2	2023-03-02	doohickey	2	888