CODEWITHSUNDEEP
mysqlsql
RomkaLTU
RomkaLTU

Reputation: 4129

How to change SQL to avoid subqueries

Here is simplified version of queryie I'm working at:

SELECT 
    r.id,
    r.nr,

    MAX(CASE WHEN (rm.meta_key = 'supplier_id') THEN (SELECT suppliers.title FROM suppliers WHERE suppliers.id = rm.meta_value) ELSE NULL END) AS supplier,
    MAX(CASE WHEN (rm.meta_key = 'client_id') THEN (SELECT clients.name FROM clients WHERE clients.id = rm.meta_value) ELSE NULL END) AS client,

FROM `registries` AS r 
INNER JOIN `registries_meta` AS rm ON `r`.`id` = `rm`.`registries_id`
GROUP BY r.id
LIMIT 100

Is it possible to avoid subquery here? I need to tell Mysql "Join registries_metas and if meta_key is client_id JOIN clients.id = meta_value and select clients.name".

Thanks.

Upvotes: 2

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I believe the logic you want uses just join:

SELECT r.id, r.nr,
       c.name as client_name, s.title as supplier
FROM registries r INNER JOIN
     registries_meta rm
     ON r.id = rm.registries_id LEFT JOIN
     clients c 
     ON rm.meta_value = c.id AND rm.meta_key = 'client_id' LEFT JOIN
     suppliers s 
     ON rm.meta_value = s.id AND rm.meta_key = 'supplier_id';

If you can have multiple clients/suppliers, you probably want all of them on one row. That suggests aggregation:

SELECT r.id, r.nr,
       GROUP_CONCAT(c.name) as client_names,
       GROUP_CONCAT(s.title) as suppliers
FROM registries r INNER JOIN
     registries_meta rm
     ON r.id = rm.registries_id LEFT JOIN
     clients c 
     ON rm.meta_value = c.id AND rm.meta_key = 'client_id' LEFT JOIN
     suppliers s 
     ON rm.meta_value = s.id AND rm.meta_key = 'supplier_id'
GROUP BY r.id, r.nr;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Try left joining to the clients table. My intuition tells me that we need to do the pivot in a subquery to find the correct meta value for each key. Then, we can left join to clients using that meta value (which should be the client ID).

SELECT
    t.id,
    t.nr,
    t.client AS client_id,
    COALESCE(c.name, '') AS client_name,
    t.supplier AS supplier_id,
    COALESCE(s.title, '') AS supplier_title
FROM
(
    SELECT 
        r.id,
        r.nr,
        MAX(CASE WHEN rm.meta_key = 'client_id' THEN rm.meta_value END) AS client,
        MAX(CASE WHEN rm.meta_key = 'supplier_id' THEN rm.meta_value END) AS supplier
    FROM registries AS r 
    INNER JOIN registries_meta AS rm
        ON r.id = rm.registries_id
    GROUP BY r.id
) t
LEFT JOIN clients AS c
    ON t.client = c.id
LEFT JOIN suppliers AS s
    ON t.supplier = s.id;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

do left join with clients and suppliers table

SELECT 
    r.id,
    r.nr,
    c.name AS client_name,s.title as supplier
FROM `registries` AS r 
INNER JOIN `registries_meta` AS rm ON `r`.`id` = `rm`.`registries_id`
left join clients c on rm.meta_value=c.id 
left join suppliers s on rm.meta_value=suppliers.id

Upvotes: 1

Related Questions