FULL OUTER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FULL OUTER JOIN

Author

Topic

johns
Starting Member

24 Posts

Posted - 2003-06-08 : 16:30:50

CREATE TABLE tblParent(
pID INT PRIMARY IDENTITY (1, 1) NOT NULL,
pName VARCHAR(50) NOT NULL,
pDescription VARCHAR(3000) )

CREATE TABLE tblChild (
cID INT PRIMARY IDENTITY (1, 1) NOT NULL,
pID INT NOT NULL,
cName VARCHAR(50) NOT NULL,
cDecription(3000) )

I want to count the number of child tables to each parent table. SoI tried this and it works, but i have to include the GROUP BY.

SELECT P.pID, P.pName, P.pDescription, COUNT(C.cID) AS Child_CNT
FROM tblParent P
FULL OUTER JOIN tblChild C ON P.pID = C.pID
WHERE tblParent.pID > 0
GROUP BY P.pID, P.pName, P.pDescription
ORDER BY P.pName ASC

If I remove the "GROUP BY" from my above SQL Query I get an error message.

1. Why do I have to add the GROUP BY?
2. Is there a better way to write this SQL Query?

Thanks,

John S.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-08 : 17:18:50

COUNT(*) is an aggregate function; it only can be used when you are GROUPing by certain fields. It is a way of summaring the values in multiple rows in a table or query. Read books on-line for more info about aggregate functions and GROUP BY's.

You have written the query almost perfectly, but you shoul be using a LEFT OUTER JOIN instead of a FULL OUTER JOIN.

SELECT P.pID, P.pName, P.pDescription, COUNT(C.cID) AS Child_CNT
FROM tblParent P
LEFT OUTER JOIN tblChild C ON P.pID = C.pID
WHERE tblParent.pID > 0
GROUP BY P.pID, P.pName, P.pDescription
ORDER BY P.pName ASC

You also could do it like this:

Select p.*,
(select count(*) from tblChild C WHERE p.pID = c.pID) as Child_CNT
FROM
tblParent P
ORDER BY p.pName ASC

- Jeff

Edited by - jsmith8858 on 06/08/2003 17:19:22

johns
Starting Member

24 Posts

Posted - 2003-06-08 : 22:23:01

>>>>>>>>>>>>>>>>
Select p.*,
(select count(*) from tblChild C WHERE p.pID = c.pID) as Child_CNT
FROM
tblParent P
ORDER BY p.pName ASC
<<<<<<<<<<<<<<<

Jeff,

Thanks! The above worked perfectly and better than my solution.

John S.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources