CODEWITHSUNDEEP
sql
Machiaveli
Machiaveli

Reputation: 27

How to show all information from sql query

i have a sql query, but i have problem with this, i would like that the sql query show me all the information in both table that i have... And it works, but when i put a condition in the sentence, i can't have the full info...

With this query, i can see all information of my table, even if i don't have info in some columns... And i need that, but i want to add a condition, and when i add the condition i can't see all.

Result: Query without condition

SELECT dbo.tblHoras.IdHora, dbo.tblHoras.Hora, dbo.tblHoras.Meta, COUNT(dbo.tblProductos.Serial) as Cantidad
FROM tblHoras full join
     tblProductos
     ON tblHoras.IdHora = tblProductos.IdHora
GROUP By tblHoras.IdHora, tblHoras.Hora, tblHoras.Meta
ORDER By tblHoras.IdHora;

And this is the query with the condition, and this doesn't bring me all the info.

SELECT dbo.tblHoras.IdHora, dbo.tblHoras.Hora, dbo.tblHoras.Meta, COUNT(dbo.tblProductos.Serial) as Cantidad
FROM tblHoras full join
     tblProductos
     ON tblHoras.IdHora = tblProductos.IdHora
WHERE tblProductos.ActualFecha = '2017-04-19'
GROUP By tblHoras.IdHora, tblHoras.Hora, tblHoras.Meta
ORDER By tblHoras.IdHora;

Result: Query with condition

Upvotes: 0

Views: 224

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Based on the fact that you are aggregating only by columns in tblHoras, I think you want a LEFT JOIN:

SELECT h.IdHora, h.Hora, h.Meta, COUNT(p.Serial) as Cantidad
FROM tblHoras h LEFT JOIN
     tblProductos p
     ON p.IdHora = p.IdHora AND p.ActualFecha = '2017-04-19'
GROUP By h.IdHora, h.Hora, h.Meta
ORDER By h.IdHora;

The filtering condition should then be in the ON clause.

Notice that I also introduced table aliases which make the query easier to write and to read.

Upvotes: 1

Related Questions