I need to write this query with out the union

[bprego]

the union duplicates the Level2ID and Level2Name and I can't use the query this way. can any one help me out, PLEASE? the code is posted below.

SELECT distinct Level2ID, Level2Name, count(Level3Name)as TOTAL_SUBS, '' as TOTAL_COMPANIES
FROM tbl_Prod_Categories
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
and Level2ID is NOT NULL
And Level1ID = MMColParam2
group by Level2ID, Level2Name
union
SELECT distinct Level2ID, Level2Name, '' as TOTAL_SUBS, count(company_id) as TOTAL_COMPANIES
FROM tbl_Prod_Categories right outer join listing
on categoryID = NewcatID
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
AND Level3ID IS NULL --Notice this is not in the upper portion of the UNION and it cant be
and Level2ID is NOT NULL
And Level1ID = MMColParam2
AND processedYN = 'Y'
group by Level2ID, Level2Name
ORDER BY Level2Name ASC


my output looks like this right now
Level2ID Level2Name TOTAL_SUBS TOTAL_COMPANIES
52 Audio-Visual 5 0
22 Broadband 0 0 --must remove!!
22 Broadband 0 14
26 Communication 5 0
27 Transmission 5 0

but what I need is this
Level2ID Level2Name TOTAL_SUBS TOTAL_COMPANIES
52 Audio-Visual 5 0
22 Broadband 0 14
26 Communication 5 0
27 Transmission 5 0
Thanks in advance.

 

[carp]

Depending on the RDBMS you are using, you might try

SELECT level2ID, Level2Name,
sum(total_subs), sum(total_companies)
FROM
(SELECT Level2ID, Level2Name, count(Level3Name)as TOTAL_SUBS, '' as TOTAL_COMPANIES
FROM tbl_Prod_Categories
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
and Level2ID is NOT NULL
And Level1ID = MMColParam2
group by Level2ID, Level2Name
union
SELECT distinct Level2ID, Level2Name, '' as TOTAL_SUBS, count(company_id) as TOTAL_COMPANIES
FROM tbl_Prod_Categories right outer join listing
on categoryID = NewcatID
WHERE level2name in (Select distinct Level2name from tbl_Prod_Categories)
AND Level4ID IS NULL
AND Level3ID IS NULL and Level2ID is NOT NULL
And Level1ID = MMColParam2
AND processedYN = 'Y'
group by Level2ID, Level2Name
)
GROUP BY level2ID, Level2Name;

 

[JamesLean]

This problem has been dealt with in the SQL Server forum: thread183-700530

bprego,
in future please don't cross-post.

--James