Hi all,

Here is my problem, I have 3 tables :

People
-------------
IDPeople
Firstname
Lastname

Cars
------------
IDPeople
Carname

Boats
------------
IDPeople
Boatname

1 person can have 0 or n car/boat
I want to a result set displaying : Firstname, Lastname, NumberOfCars,
NumberOfBoats

I have two queries, but i want to merge the results in one. how can i do
this ?

This one gives me FIRSTNAME, LASTNAME and CARCOUNT
------------------------------------
SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,
COUNT(dbo.Cars.CarName) AS CARCOUNT
FROM dbo.People LEFT OUTER JOIN
dbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeople
GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName

This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
------------------------------------
SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,
COUNT(dbo.Boats.BoatName) AS BOATCOUNT
FROM dbo.People LEFT OUTER JOIN
dbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeople
GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName



Thanks in advance
Phil

Philippe,

You can UNION the queries and SUM the result or, more simply:

SELECT
p.IDPeople,
p.FirstName,
p.LastName,
COUNT(DISTINCT c.CarName) AS CarCount,
COUNT(DISTINCT b.BoatName) AS BoatCount
FROM
People AS p LEFT JOIN
Cars AS c ON p.IDPeople = c.IDPeople LEFT JOIN
Boats AS b ON p.IDPeople = b.IDPeople
GROUP BY
p.IDPeople,
p.FirstName,
p.LastName;


Hope that helps,
Rich



"Philippe Bouffaut" <-killspam-pbouffaut-nospam-@hotmail.com> wrote in
message news:g7cUa.10243$Wh.1088752@news20.bellglobal.com. ..
> Hi all,
>
> Here is my problem, I have 3 tables :
>
> People
> -------------
> IDPeople
> Firstname
> Lastname
>
> Cars
> ------------
> IDPeople
> Carname
>
> Boats
> ------------
> IDPeople
> Boatname
>
> 1 person can have 0 or n car/boat
> I want to a result set displaying : Firstname, Lastname, NumberOfCars,
> NumberOfBoats
>
> I have two queries, but i want to merge the results in one. how can i do
> this ?
>
> This one gives me FIRSTNAME, LASTNAME and CARCOUNT
> ------------------------------------
> SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,
> COUNT(dbo.Cars.CarName) AS CARCOUNT
> FROM dbo.People LEFT OUTER JOIN
> dbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeople
> GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName
>
> This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
> ------------------------------------
> SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,
> COUNT(dbo.Boats.BoatName) AS BOATCOUNT
> FROM dbo.People LEFT OUTER JOIN
> dbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeople
> GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName
>
>
>
> Thanks in advance
> Phil
>
>

Thanks a lot Rich, that's it.
I missed the "DISTINCT" in the count.

Regards

"Rich Dillon" <richdillon@mindspring.com> wrote in message
news:bfs28e$k95$1@slb4.atl.mindspring.net...
> Philippe,
>
> You can UNION the queries and SUM the result or, more simply:
>
> SELECT
> p.IDPeople,
> p.FirstName,
> p.LastName,
> COUNT(DISTINCT c.CarName) AS CarCount,
> COUNT(DISTINCT b.BoatName) AS BoatCount
> FROM
> People AS p LEFT JOIN
> Cars AS c ON p.IDPeople = c.IDPeople LEFT JOIN
> Boats AS b ON p.IDPeople = b.IDPeople
> GROUP BY
> p.IDPeople,
> p.FirstName,
> p.LastName;
>
>
> Hope that helps,
> Rich
>
>
>
> "Philippe Bouffaut" <-killspam-pbouffaut-nospam-@hotmail.com> wrote in
> message news:g7cUa.10243$Wh.1088752@news20.bellglobal.com. ..
> > Hi all,
> >
> > Here is my problem, I have 3 tables :
> >
> > People
> > -------------
> > IDPeople
> > Firstname
> > Lastname
> >
> > Cars
> > ------------
> > IDPeople
> > Carname
> >
> > Boats
> > ------------
> > IDPeople
> > Boatname
> >
> > 1 person can have 0 or n car/boat
> > I want to a result set displaying : Firstname, Lastname, NumberOfCars,
> > NumberOfBoats
> >
> > I have two queries, but i want to merge the results in one. how can i do
> > this ?
> >
> > This one gives me FIRSTNAME, LASTNAME and CARCOUNT
> > ------------------------------------
> > SELECT dbo.People.IDPeople, dbo.People.FirstName,
dbo.People.LastName,
> > COUNT(dbo.Cars.CarName) AS CARCOUNT
> > FROM dbo.People LEFT OUTER JOIN
> > dbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeople
> > GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName
> >
> > This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
> > ------------------------------------
> > SELECT dbo.People.IDPeople, dbo.People.FirstName,
dbo.People.LastName,
> > COUNT(dbo.Boats.BoatName) AS BOATCOUNT
> > FROM dbo.People LEFT OUTER JOIN
> > dbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeople
> > GROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName
> >
> >
> >
> > Thanks in advance
> > Phil
> >
> >
>
>