Merge the results of two queries

[Philippe Bouffaut]

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.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name,
COUNT(dbo.Cars. CarName) AS CARCOUNT
FROM dbo.People LEFT OUTER JOIN
dbo.Cars ON dbo.People.IDPe ople = dbo.Cars.IDPeop le
GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name

This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
------------------------------------
SELECT dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name,
COUNT(dbo.Boats .BoatName) AS BOATCOUNT
FROM dbo.People LEFT OUTER JOIN
dbo.Boats ON dbo.People.IDPe ople = dbo.Boats.IDPeo ple
GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name



Thanks in advance
Phil

[Rich Dillon]

Re: Merge the results of two queries

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.1024 3$Wh.1088752@ne ws20.bellglobal .com...[color=blue]
> 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.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name,
> COUNT(dbo.Cars. CarName) AS CARCOUNT
> FROM dbo.People LEFT OUTER JOIN
> dbo.Cars ON dbo.People.IDPe ople = dbo.Cars.IDPeop le
> GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name
>
> This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
> ------------------------------------
> SELECT dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name,
> COUNT(dbo.Boats .BoatName) AS BOATCOUNT
> FROM dbo.People LEFT OUTER JOIN
> dbo.Boats ON dbo.People.IDPe ople = dbo.Boats.IDPeo ple
> GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name
>
>
>
> Thanks in advance
> Phil
>
>[/color]

[Philippe Bouffaut]

Re: Merge the results of two queries

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

Regards

"Rich Dillon" <richdillon@min dspring.com> wrote in message
news:bfs28e$k95 $1@slb4.atl.min dspring.net...[color=blue]
> 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.1024 3$Wh.1088752@ne ws20.bellglobal .com...[color=green]
> > 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.IDPe ople, dbo.People.Firs tName,[/color][/color]
dbo.People.Last Name,[color=blue][color=green]
> > COUNT(dbo.Cars. CarName) AS CARCOUNT
> > FROM dbo.People LEFT OUTER JOIN
> > dbo.Cars ON dbo.People.IDPe ople = dbo.Cars.IDPeop le
> > GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name
> >
> > This one gives me FIRSTNAME, LASTNAME and BOATCOUNT
> > ------------------------------------
> > SELECT dbo.People.IDPe ople, dbo.People.Firs tName,[/color][/color]
dbo.People.Last Name,[color=blue][color=green]
> > COUNT(dbo.Boats .BoatName) AS BOATCOUNT
> > FROM dbo.People LEFT OUTER JOIN
> > dbo.Boats ON dbo.People.IDPe ople = dbo.Boats.IDPeo ple
> > GROUP BY dbo.People.IDPe ople, dbo.People.Firs tName, dbo.People.Last Name
> >
> >
> >
> > Thanks in advance
> > Phil
> >
> >[/color]
>
>[/color]