SQL subquery - Replicated values

Asked

Viewed 39 times

0

I am working with 3 tables, the SUPPLIED table contains the name and code of the suppliers and the other two tables have the data I want to work. The problem is when I try to make a loop so I can use the subquerys. Because if you notice in the table the total result = 3 and the result with filter is 6. It makes no sense, because there are 6 values.

SELECT F.RASSOC,
(SELECT COUNT(DTENG) 
FROM PARHLISE 
WHERE FORNECE = F.CODIGO) AS TOTAL_PARHLISE,

(SELECT COUNT(P.DTENG) 
FROM PARHLISE AS P
INNER JOIN HISTLISE AS H ON H.FORNECE = P.FORNECE
WHERE P.FORNECE = F.CODIGO AND P.DTENG > H.DTEMI) AS PARHLISE_ATRASO

FROM FORNECED AS F
LEFT JOIN PARHLISE AS PH ON PH.FORNECE = F.CODIGO
LEFT JOIN PARCLISE AS PC ON PC.FORNECE = F.CODIGO
GROUP BY F.CODIGO, F.RASSOC

inserir a descrição da imagem aqui

sql

  • Note that you are making a junction and if there is more than one record in HISTLYSIS for the same PROVIDES then duplicities may occur. Maybe it’s not the case that you use junction for your condition.

  • But without the junction I don’t get the data that only exists in histlise

  • You use the H.DTEMI, you need the DTEMI value of ALL records from HISTLISE that link to FORNEECD? Put the table structure and goal with this query, maybe with this someone can see an easier way to do this instead of this query.

  • Why are you using LEFT JOIN? could not be only SELECT F.RASSOC, (SELECT COUNT(DTENG) FROM PARHLISE WHERE PROVIDES = F.CODE) AS TOTAL_PARHLISE, (SELECT COUNT(P.DTENG) FROM PARHLISE AS P INNER JOIN HISTLISE AS H ON H.PROVIDES = P.PROVIDES WHERE P.PROVIDES = F.CODE AND P.DTENG > H.DTEMI) AS PARHLISE_ATRASO FROM SUPPLIED AS F

1 answer

0

Test replace this junction only by checking if there is record in HISTLISE that satisfies the condition:

(SELECT COUNT(P.DTENG) FROM PARHILISE AS P
WHERE EXISTS (SELECT * FROM HISTLISE AS H WHERE P.FORNECE = F.CODIGO AND P.DTENG > H.DTEMI)) AS PARHLISE_ATRASO

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.