"Cristian Lefter" <nospam_CristianLefter@hotmail.com> wrote in message
news:OD06Q%235AFHA.3024@TK2MSFTNGP10.phx.gbl...
> Replace
> "INNER JOIN dbo.Child2 ON dbo.Child1.IDC1 = dbo.Child2.IDC2"
> with "LEFT JOIN dbo.Child2 ON dbo.Child1.IDC1 = dbo.Child2.IDC2"
> or "LEFT OUTER JOIN dbo.Child2 ON dbo.Child1.IDC1 = dbo.Child2.IDC2" if
you
> prefer to use the long form of joins.
> (INNER JOIN = JOIN, LEFT OUTER JOIN = LEFT JOIN, RIGHT OUTER JOIN = RIGHT
> JOIN, FULL OUTER JOIN = FULL JOIN )
>
> Cristian Lefter, SQL Server MVP
>
> "qaz" <ngnews@uslex.net> wrote in message
> news:exvfdg0AFHA.2932@TK2MSFTNGP10.phx.gbl...
> >I have 3 related tables, one "parent", a child table and a "grandchild"
> > table.
> >
> > I want to return a list of child records and also a count of the number
of
> > records in the grandchild table.
> >
> > I have a query that does this BUT it will not return a record if there
are
> > no records in the grandchild (in other words, if the grandchild record
> > count
> > = 0)
> >
> > How do I create a query that will return a list of all child records
with
> > their grandchild count even if the grandchild count = 0?
> >
> > Here is my query:
> >
> > SELECT dbo.Parent.PName, dbo.Child1.C1Name, dbo.Child1.IDC1,
COUNT(*)
> > FROM dbo.Parent INNER JOIN
> > dbo.Child1 ON dbo.Parent.IDParent = dbo.Child1.IDC1 INNER JOIN
> > dbo.Child2 ON dbo.Child1.IDC1 = dbo.Child2.IDC2
> > WHERE dbo.Child2.RecDate >= getdate()
> > GROUP BY dbo.Parent.PName, dbo.Child1.C1Name, bo.Child1.IDC1
> >
> > Any help is appreciated.
> >
> >
>
>

"qaz" <ngnews@uslex.net> wrote in message
news:exvfdg0AFHA.2932@TK2MSFTNGP10.phx.gbl...
>I have 3 related tables, one "parent", a child table and a "grandchild"
> table.
>
> I want to return a list of child records and also a count of the number of
> records in the grandchild table.
>
> I have a query that does this BUT it will not return a record if there are
> no records in the grandchild (in other words, if the grandchild record
> count
> = 0)
>
> How do I create a query that will return a list of all child records with
> their grandchild count even if the grandchild count = 0?
>
> Here is my query:
>
> SELECT dbo.Parent.PName, dbo.Child1.C1Name, dbo.Child1.IDC1, COUNT(*)
> FROM dbo.Parent INNER JOIN
> dbo.Child1 ON dbo.Parent.IDParent = dbo.Child1.IDC1 INNER JOIN
> dbo.Child2 ON dbo.Child1.IDC1 = dbo.Child2.IDC2
> WHERE dbo.Child2.RecDate >= getdate()
> GROUP BY dbo.Parent.PName, dbo.Child1.C1Name, bo.Child1.IDC1
>
> Any help is appreciated.
>
>