CODEWITHSUNDEEP
mysqlsqlsubqueryalias
GGrassiant
GGrassiant

Reputation: 119

SQL reuse a subquery 'AS' as a parameter for another subquery

I am new to SQL and try to re-use an alias/subquery I made as a parameter for another subquery.

For a certain period of time I want all the customers who have made a purchase, I get the date of the last purchase but now I am trying to pass this date to the invoices in order to get the name of the salesperson associated to this invoice.

So far I have this:

SELECT c.id,
       c.firstname,
       c.lastname,
       c.language,
       c.sex,
       c.company,
       c.city,
       c.postal_code,
       c.email,
       c.created_at,
       (SELECT max(`created_at`) FROM invoices WHERE client_id=c.id) AS last_purchase_date,
[...]
FROM 
    clients c
JOIN 
    boutiques b ON b.id = c.boutique_id
JOIN 
    brands br ON br.id = b.brand_id
[...]

and would like something like: 

SELECT c.id,
       c.firstname,
       c.lastname,
       c.language,
       c.sex,
       c.company,
       c.city,
       c.postal_code,
       c.email,
       c.created_at,
       u.name
       (SELECT max(`created_at`) FROM invoices WHERE client_id=c.id) AS last_purchase_date,
       (SELECT id FROM invoices WHERE created_at = last_purchase_date) AS last_invoice_id
       (SELECT name FROM users u WHERE id=last_invoice.user_id) AS sales_advisor
[...]
FROM 
    clients c
JOIN 
    boutiques b ON b.id = c.boutique_id
JOIN 
    users u ON u.boutique_id = b.id
JOIN 
    brands br ON br.id = b.brand_id
[...]

Thanks in advance!

Upvotes: 1

Views: 406

Answers (1)

Parfait
Parfait

Reputation: 107652

Consider migrating those subqueries into derived tables (i.e., queries in FROM or JOIN clauses instead of SELECT clause). In fact, two of those subqueries can become whole tables: invoices and second users.

SELECT c.id,
       c.firstname,
       c.lastname,
       c.language,
       c.sex,
       c.company,
       c.city,
       c.postal_code,
       c.email,
       c.created_at,
       u.name,
       agg.last_purchase_date,
       i.id AS last_invoice_id,
       u2.name AS sales_advisor
[...]
FROM 
    clients c
JOIN 
    boutiques b ON b.id = c.boutique_id
JOIN 
    users u ON u.boutique_id = b.id
JOIN 
    brands br ON br.id = b.brand_id
JOIN
    (
     SELECT client_id, max(`created_at`) as last_purchase_date
     FROM invoices
     GROUP BY client_id
    ) agg
  ON c.id = agg.client_id
JOIN 
    invoices i ON i.client_id = agg.client_id
               AND i.created_at = agg.last_purchase_date
JOIN 
    users u2 ON u2.id = i.user_id
[...]

Upvotes: 2

Related Questions