CODEWITHSUNDEEP
mysqlsqljoin
gugoan
gugoan

Reputation: 780

Correct JOIN in query

In the low query, how do I display all records in the "users" table? Currently only those with a sales record are being displayed

    SELECT
    d.operator_id as num_carteira,
    u.name_carteira as Carteira,
    sum(d.`value`) AS Valor,
    sum(d.`quantity`) AS Qtde
    FROM daily_productivity d
    RIGHT JOIN product p
    ON d.product_id = p.id
    RIGHT JOIN `user` u
    ON d.operator_id = u.id
    WHERE YEAR(date) = 2019 AND MONTH(date) = 06 AND d.daily_productivity_status_id = 2 AND p.mobilizador_id = 1 AND u.role_id = 6 
    ORDER BY u.name_carteira

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use left joins and put the table where you want to keep all the rows first -- that would be user. Conditions on this table can be filtered in the where clause.

Conditions on all other tables should go in the on clause:

SELECT d.operator_id as num_carteira, u.name_carteira as Carteira,
       sum(d.`value`) AS Valor, sum(d.`quantity`) AS Qtde
FROM user u LEFT JOIN
     daily_productivity d
     ON d.operator_id = u.id AND
        d.date >= '2019-06-01' AND
        d.date < '2019-07-01' AND
        d.daily_productivity_status_id = 2 LEFT JOIN
     product p
     ON d.product_id = p.id AND p.mobilizador_id = 1
WHERE u.role_id = 6 
GROUP BY d.operator_id, u.name_carteira 
ORDER BY u.name_carteira;

This guesses that date comes from the daily_productivity table.

Note that I changed the date logic so it is more index-compatible. I would also recommend using u.id rather than d.operator_id in the SELECT because the latter could be NULL.

If you do, indeed, want all users in user then remove the where clause.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You should not use left / right joined tables columns in where clause you should add these condition to the related ON clause 

    SELECT
    d.operator_id as num_carteira,
    u.name_carteira as Carteira,
    sum(d.`value`) AS Valor,
    sum(d.`quantity`) AS Qtde
    FROM daily_productivity d
    LEFT  JOIN product p  ON d.product_id = p.id AND  p.mobilizador_id = 1 
    LEFT JOIN `user` u   ON d.operator_id = u.id AND  u.role_id = 6 
    WHERE YEAR(date) = 2019 AND MONTH(date) = 06 AND d.daily_productivity_status_id = 2 
    ORDER BY u.name_carteira

and you should use also proper group by 

    SELECT
    d.operator_id as num_carteira,
    u.name_carteira as Carteira,
    sum(d.`value`) AS Valor,
    sum(d.`quantity`) AS Qtde
    FROM daily_productivity d
    LEFT  JOIN product p  ON d.product_id = p.id AND  p.mobilizador_id = 1 
    LEFT JOIN `user` u   ON d.operator_id = u.id AND  u.role_id = 6 
    WHERE YEAR(date) = 2019 AND MONTH(date) = 06 AND d.daily_productivity_status_id = 2 
    GROUP BY d.operator_id as num_carteira,  u.name_carteira as Carteira,
    ORDER BY u.name_carteira

the use of aggregation function without group by in most db (and for particular db version) is not allowed and ca raise error

Upvotes: 2

Related Questions