I want to do this???? - 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)

I want to do this????

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-15 : 18:49:15

Hi,

I'm having trouble figuring out how I do the following select statment to retrieve a recordset that includes a counted total from another table with one statement.

Here is what I want to do.

SELECT CatID, CatPID, CatName, (SELECT COUNT(*) FROM IPG_LISTINGS WHERE LCatPID = @@IDENTITY) As Total
FROM IPG_CATEGORIES
WHERE CatPID IS Null
ORDER BY CatID

I would like the total of the Count(*) to be inserted into the recordset as another field ascociated with the CatID of each row. I need to count how many CatID's are located in the other table that match the row of the recordset.

I hope I explained this right. I would certainly love to hear suggestions.

Thanks

JB

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-15 : 20:05:47

Hi JB

I'm guessing you want something like:
select a.CatID, a.CatPID, a.CatName, count(b.lCatPID) as Total
from IPG_CATEGORIES a left join IPG_LISTINGS b
on a.CatID = b.CatID
group by a.CatID, a.CatPID, a.CatName
order by a.CatID, a.CatPID, a.CatName

but can't really be sure until you post some table definitions...
(don't understand why you have
WHERE LCatPID = @@IDENTITY
OR
WHERE LCatPID IS NULL)
??

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-15 : 20:43:13

Hi,

Maybe this will explain.

This is exactly what I want to do, however there is several selects and 10 updates.

CREATE TABLE #Directory (
CatID int IDENTITY,
CatPID int,
CatName nvarchar(100),
ListTotal int
)

INSERT INTO #Directory
SELECT CatPID, CatName, (0)
FROM IPG_CATEGORIES
WHERE CatPID IS NULL

DECLARE @Counter int
SET @Counter = 1

WHILE @Counter < 11
BEGIN
UPDATE #Directory
SET ListTotal = (SELECT COUNT(*) FROM IPG_LISTINGS WHERE LCatPID = @Counter)
WHERE CatID = @Counter

SET @Counter = @Counter + 1
END

SELECT *
FROM #Directory

DROP TABLE #Directory

Here are My Tables

IPG CATEGORIES
CatID CatPID CatName
----------- ----------- ------------------------
1 NULL Production Companies
2 NULL Pre-Production
3 NULL Crew
4 NULL Location and Travel
5 NULL Stages, Studios and Sets
6 NULL Camera and Sound Equipment
7 NULL Grip and Lighting Equipment
8 NULL Props and Wardrobe
9 NULL Post Production
10 NULL Agencies
11 10 Advertising Agencies
12 1 Animation Companies
13 1 Production Companies
14 1 Corporate Video Companies
15 1 Corporate Event Companies
16 10 Director's Agencies
17 1 Production Services
18 10 Public Relations
19 1 Record Companies

..................... ETC......................

58 4 Airlines
59 4 Airports

IPG_LISTINGS
LID LCatID LCatPID LAccountID
----------- ----------- ----------- -----------
38 12 1 2
39 13 1 2
40 14 1 2
41 18 10 16666
42 19 2 16666
43 12 1 16666

Here is the results set that I want. Selecting all of the IPG_CATEGORIES with CatPID as Null and then the ascociated total of those "subcategories" listed under ListTotal. Is it basically a list of all of the top categories with the total users who are listed in all of the ascociated subcategories. CatPID stands for Category Parent ID. And If that is Null that means that that is the highest on the directory you can go.

CatID CatPID CatName ListTotal
----------- ----------- ------------------------------ -----------
1 NULL Production Companies 4
2 NULL Pre-Production 1
3 NULL Crew 0
4 NULL Location and Travel 0
5 NULL Stages, Studios and Sets 0
6 NULL Camera and Sound Equipment 0
7 NULL Grip and Lighting Equipment 0
8 NULL Props and Wardrobe 0
9 NULL Post Production 0
10 NULL Agencies 1

I hope this explains. Sometimes trying to explain is harder than the SQL! I tried your approach and got the following error.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'CatID'.

JB

Edited by - JBelthoff on 12/15/2002 20:49:27

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-15 : 22:31:30

The error was because in table IPG_LISTINGS - the field is LCatID not CatID - I assumed they were the same as you said

quote:
another field ascociated with the CatID of each row

So does that mean you've worked it out? Sorry - I can't tell whether you still have aquestion or not...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-15 : 23:57:20

Hi rrb,

I have it so it is doing what I want now. However as you can see from the above procedure there are many steps in the process.

1. Create Temp Table -- OK
2. Populate it with the basic info via INSERT/SELECT -- OK
3. Update the rows individually with 10 update calls in a "WHILE" loop -- ???
4. SELECT the final info from the temp table. -- OK

I would like to make step 3 more efficient if that is at all possible. That's the part of the query that I was trying to slim down and also trying to incorporate into step 2 doing away with step 3 completely.

I hope that makes sense, it getting pretty late here in Connecticut!

If you have any suggestions I would love to hear them.

Thanks,

JB

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-16 : 00:12:47

aaahhhhh!

Your join is a.CatID = b.LCatPID

OK - here it is:
select a.catid, a.catPID, a.catname, count(b.LID)
from IPG_CATEGORIES a left join IPG_LISTINGS b
on a.Catid = b.LCatPID
where a.catPID is null
group by a.catid, a.catPID, a.catname
order by a.catid, a.catPID, a.catname

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-16 : 10:20:25

Hi Rob,

That works great.

Is there anyway to add an additional column to the result set so that it calculates how many subcategories there are for each of the listed categories?

If you could lead me in the direction to figure that out myself I would appreciate it. Of course, you can also show me the code but I would rather try to figure it out for myself and then if I am still stuck I can ask again.

Would I do an additional right Join back on IPG_CATEGORIES? Just a thought.

Thanks,

JB

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-16 : 13:52:52

Hi Again,

OK, here it is.

This statement gives me the result set containing what I need.

SELECT a.CatID, a.catPID, a.catname, COUNT(b.LID) ListTotal
FROM IPG_CATEGORIES a
LEFT JOIN IPG_LISTINGS b on a.Catid = b.LCatPID
WHERE a.catPID IS NULL
GROUP BY a.catid, a.catPID, a.catname
ORDER BY a.catid

This statement gives me a second result set on the subcategory count

SELECT Count(c.CatPID) As SubCatTotal
FROM IPG_CATEGORIES a
Left join IPG_CATEGORIES c on a.CatID = c.CatPID
WHERE a.CatPID IS NULL
GROUP BY a.CatID, a.CatPID, a.CatNAme
ORDER BY a.CatID

I've tried to combine them into one SELECT with no luck.

Is there a way to combine these 2 statements into one and get the results I need. Which are...

CatID catPID catname ListTotal SubCatCount
----------- ----------- ----------------------------- ----------- -----------
1 NULL Production Companies 4 7
2 NULL Pre-Production 1 13
3 NULL Crew 0 21
4 NULL Location and Travel 0 3
5 NULL Stages, Studios and Sets 0 0
6 NULL Camera and Sound Equipment 0 0
7 NULL Grip and Lighting Equipment 0 0
8 NULL Props and Wardrobe 0 0
9 NULL Post Production 0 0
10 NULL Agencies 1 5

Thanks,

JB

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-16 : 18:34:38

Hi J - glad to hear it went OK.

To be honest, there probably is some sneaky way - and maybe one of the gurus will see the light - but I'm too lazy to be smart...if it was me, I'd just take your two queries and make them subqueries, then I'd join them to sync them up.

I've got the code if you want it.

PS When I ran the subcategory code, I got
SubCatTotal
----------
6
0
0
0
0
0
0
0
0
3

These are different to your results - is this because you have extra data in your table? Or have I missed something?

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-16 : 19:21:07

Hi Rob,

Are you saying that I should create two derived tables and then do a select joining the two derived tables?

And yes, you results are different because I did not list all of my data.

JB

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-16 : 19:23:36

CREATE TABLE #IPG_CATEGORIES(CatID int,CatPID int,CatName nvarchar(50))
CREATE TABLE #IPG_LISTINGS(LID int,LCatID int,LCatPID int,LAccountID int)

INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (1,NULL,'Production Companies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (2,NULL,'Pre-Production')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (3,NULL,'Crew')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (4,NULL,'Location and Travel')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (5,NULL,'Stages, Studios and Sets')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (6,NULL,'Camera and Sound Equipment')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (7,NULL,'Grip and Lighting Equipment')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (8,NULL,'Props and Wardrobe')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (9,NULL,'Post Production')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (10,NULL,'Agencies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (11,10,'Advertising Agencies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (12,1,'Animation Companies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (13,1,'Production Companies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (14,1,'Corporate Video Companies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (15,1,'Corporate Event Companies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (16,10,'Director''s Agencies')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (17,1,'Production Services')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (18,10,'Public Relations')
INSERT INTO #IPG_CATEGORIES (CatID, CatPID, CatName) VALUES (19,1,'Record Companies')

INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (38,12,1,2)
INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (39,13,1,2)
INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (40,14,1,2)
INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (41,18,10,16666)
INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (42,19,2,16666)
INSERT INTO #IPG_LISTINGS (LID, LCatID, LCatPID, LAccountID) VALUES (43,12,1,16666)

SELECT a.CatID, a.CatName,
(select count(*) from #IPG_LISTINGS b where a.CatID = b.LCatPID) ListTotal,
(select count(*) from #IPG_CATEGORIES c where a.CatID = c.CatPID) SubCatCount
FROM #IPG_CATEGORIES a
WHERE a.CatPID Is Null

DROP TABLE #IPG_CATEGORIES
DROP TABLE #IPG_LISTINGS

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-16 : 19:26:06

Thanks Valter

Yep JB - exactly as Valter has done. Oh well, I guess you won't be able to work it out for yourself now....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-16 : 19:42:17

quote:

CatID catPID catname ListTotal SubCatCount
----------- ----------- ----------------------------- ----------- -----------
1 NULL Production Companies 4 7
2 NULL Pre-Production 1 13
3 NULL Crew 0 21
4 NULL Location and Travel 0 3
5 NULL Stages, Studios and Sets 0 0
6 NULL Camera and Sound Equipment 0 0
7 NULL Grip and Lighting Equipment 0 0
8 NULL Props and Wardrobe 0 0
9 NULL Post Production 0 0
10 NULL Agencies 1 5

My pleasure rrb but I must say you did most of the work it's a lot easier to understand what people want when they give you expected results. I read this post when it was in it's infancy and I just put it off for later due to question but you stuck it out and deserve all the credit.

I hope everyone is listening and from now on posts nice ddl, sample data and expected results.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-16 : 20:25:00

Hi Again,

Thanks Rob and Valter.

I must say it seems easy when you guys do it. I'll post the expected results sooner next time. Until than, I'll be pluggin away.

JB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources