How do I avoid sub-query duplication in my SQL query?

Question

I'm working with MariaDB, and I'm trying to get data about my sales.
Aside from general data, I want to get further data about both widgets and doohickeys.

The issue is, my customers aren't ordinary individuals; they are buyers, who represent different clients.

What interests me is the connection between the sales and my clients. The buyers are merely the ones who are on record of having bought.

This is the core of what I have so far:

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

As you can see, I repeated SELECT buyer_id FROM clients WHERE general.buyer_id = clients.buyer_id; I want to know about the clients who bought the widgets and the doohickeys, but I don't have that directly in my tables.

I've tried a number of things that didn't work.

• Left Joins gave me many rows with many nulls, as opposed to the few rows I need, filled with useful data.
• WITH (CTEs) didn't work for me, because general.buyer_id isn't defined at the start of the query.
• Correlated subqueries: reading the manual just made me more confused.

How can I avoid duplicating this subquery?

[My MariaDB version is listed as 10.6.4-MariaDB-1:10.6.4+maria~focal.]

---

I appreciate the request for a minimal reproducible example.
I created an SQL Fiddle at http://sqlfiddle.com/#!9/2fb6b6/2.

Copying here, for those that prefer it here. Schema:

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),
  (555, 2);

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

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

The essential query is the one above.

Results (the one row I want):

buyer_id	date_sold	widget_id	widget_buyer	doohickey_id	doohickey_buyer
2	2023-03-02	555	2	888	2

Answer 1

Your question is still rather ambiguous as to what to do when one buyer buys multiple widgets and/or doohickeys.

So, first of all, to avoid the repeated IN() correlated sub-query, I'd just include a JOIN on the client table...

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

fiddle

That still 'explodes' if the buyer purchases multiple widgets and/or doohickeys, so I'd normalise those two tables in to a single table (item) with an additional column denoting which type of item it is...

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

fiddle (showing 'explosion' of rows, and this fix...