SQL query doesnt give desired output-CodePudding

I am trying to create an SQL query, but when I left join an additional table (c) with conditions it reduces the number of rows.

SELECT a.id, a.naziv as nazivOperacije, b.NNaziv as nazivArtikla, b.sifra as sifraArtikla,
       norma, idArtikla
FROM artikli_rad a 
LEFT JOIN artikli b ON a.idArtikla = b.id
ORDER BY a.idArtikla ASC

Query below works great. I would like to keep the records I get, but I would like to JOIN another table. Once I do that it reduces number of rows.

SELECT a.id, a.naziv as nazivOperacije, b.Naziv as nazivArtikla, b.sifra as sifraArtikla,
       norma, idArtikla, sum(c.kolicina) as kolRadnogNaloga
FROM artikli_rad a 
LEFT JOIN artikli b ON a.idArtikla = b.Id
LEFT JOIN radni_nalozi c ON a.idArtikla = c.idArtikal   
WHERE c.status = 1
ORDER BY a.idArtikla ASC

How I can show all rows from first query and attach table radni_nalozi c with where condition that sums only quantity with status = 1?

CodePudding user response：

try this

With Outer Join, if you put in where clause then it will be treated as inner join.

$stmt = "SELECT a.id, a.naziv as nazivOperacije, b.Naziv as nazivArtikla, b.sifra as sifraArtikla, norma, idArtikla, sum(c.kolicina) as kolRadnogNaloga
FROM artikli_rad a 
LEFT JOIN artikli b ON a.idArtikla = b.Id
LEFTJOIN radni_nalozi c ON a.idArtikla = c.idArtikal   and c.status = 1
ORDER BY a.idArtikla ASC";

CodePudding user response：

You outer-join radni_nalozi. This means that in case there is no match, the original row is kept and all radni_nalozi columns are null. Then you limit your results by:

WHERE c.status = 1

This dismisses all outer-joined rows, because their status is null. You have turned your outer join into an inner join.

What you want instead is the condition to be part of the join clause:

LEFT JOIN radni_nalozi c ON a.idArtikla = c.idArtikal AND c.status = 1

But as P.Salmon just pointed out, your query is invalid, because of a malformed aggregation. MySQL should raise a syntax error, but obviously you have not SET sql_mode = 'ONLY_FULL_GROUP_BY', which you should in order to have the DBMS prevent you from writing such invalid queries. With sum(c.kolicina) and no GROUP BY clause, you tell the DBMS to limit your results to a single row containing the sum. But what values for a.id, a.naziv, etc. is the DBMS supposed to show then?

It seems you want to join the radni_nalozi sums:

SELECT 
  ar.id, 
  ar.naziv as nazivOperacije, 
  a.Naziv as nazivArtikla, 
  a.sifra as sifraArtikla, 
  a.norma, 
  ar.idArtikla, 
  rn.sum_kolicina AS kolRadnogNaloga
FROM artikli_rad ar 
JOIN artikli a ON a.id = ar.idArtikla
LEFT JOIN 
(
  SELECT idArtikal, SUM(kolicina) AS sum_kolicina
  FROM radni_nalozi
  WHERE status = 1
  GROUP BY idArtikal
) rn ON rn.idArtikla = ar.idArtikal   
ORDER BY ar.idArtikla ASC;

I have also used mnemonic alias names. names like a, b, and c don't make the query more readable as alias names are supposed to do, but make it less readable.

I also turned the outer join on artikli into an inner join, because there must be no artikli_rad row without a match in artikli in a properly set up database.