MS SQL Join Query - on a Filtered Table

Ultimatly this is for a .Net application that requires an SQL statement or stored procedure,  

I have a single table that has two Fields.  

ContactCategoriesTable

    ContactServiceID (Number)
   CategoryName (Text)

Both of these together are unique for a single record.  So a typlical data set looks like this


1  red
1  blue
1  green
2  red
2  yellow
3  red
3  blue
4  yellow

My application requires me to list ALL of the CategoryName and identify which ContactServiceID has an association. So a result for me entering the ContactServiceID of 3 (using the above example data set) would return the rows:


red      3
blue    3
green  <null> (Prefer it enter 0 as this is better to code for)
yellow <null>

So I thought I would create a select distinct for a single list of all of the CategoryName's and save it as a view then create a join that filtered only the number 3 ContactServiceID, but I am only getting the two records that have the "3"  I need ALL of the other possible CategoryNames.  

I hope someone could help with this Query.  I don't have to use the View as long as the end result table is how I need it.  I would much rather use a single table than have to create a View, but either if fine.

Thanks
Jason
LVL 1
Jason JonesGIS/APP DevAsked:
Who is Participating?
 
SimonCommented:
Here's a version where you don't have to enter the variable twice (by using a CTE):
with cte (CategoryName,ContactServiceID) as 
(select CategoryName,ContactServiceID from ContactCategoriesTable
where contactserviceid=3)
select * from cte
union 
select distinct categoryname,0
from ContactCategoriesTable where categoryname not in (select CategoryName from cte)

Open in new window

0
 
UnifiedISCommented:
The problem is your "Where ID = 3" is effectively undermining your "select distinct" in either your view or the query that uses the view. Here is a way that skips the view. The "3" criterion is part of the join so it doesn't interfere with the select distinct.

SELECT A.CategoryName,
CCT.ContactServiceID
FROM (SELECT DISTINCT CategoryName FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  CCT
ON CCT.CategoryName = V.CategoryName
AND CCT.ContactServiceID  = 3
0
 
SimonCommented:
Here's a working method. No doubt someone else will be along with a neater solution...

select CategoryName,ContactServiceID from tempT1
where contactserviceid=3
union 
select distinct categoryname,0
from tempt1 where categoryname not in (select CategoryName from tempT1
where contactserviceid=3)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jason JonesGIS/APP DevAuthor Commented:
Thanks Unified.  I tried your solution assuming the CCT is the ContactCategoryTable table I wasn't able to figure what the V was.  I tried  


SELECT A.CategoryName,
ContactCategoriesTable.ContactServiceID
FROM (SELECT DISTINCT CategoryName FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  ContactCategoriesTable
ON ContactCategoriesTable.CategoryName = ContactCategoriesTable.CategoryName
AND ContactCategoriesTable.ContactServiceID  = 3

Open in new window


The result set was all of the Categories, but each record has "3" in the ContactServiceID field

Simon,

I tried your solution

select CategoryName,ContactServiceID from ContactCategoriesTable
where contactserviceid=3
union 
select distinct categoryname,0
from ContactCategoriesTable where categoryname not in (select CategoryName from ContactCategoriesTable
where contactserviceid=3)

Open in new window


It does work, although I'd rather not have to enter the variable twice in the same query, but it DOES work. So I will certainly award you some points on this.
0
 
UnifiedISCommented:
Oh yeah, V should be A. Also, I tweaked the 'A' query and the source for the ContactServiceID. Please try it now.

SELECT A.CategoryName,
A.ContactServiceID
FROM (SELECT DISTINCT CategoryName, ContactServiceID FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  ContactCategoriesTable
ON ContactCategoriesTable.CategoryName = A.CategoryName
AND ContactCategoriesTable.ContactServiceID  = 3
0
 
Jason JonesGIS/APP DevAuthor Commented:
thanks I figured that out too :)  trying to find a way to populate the nulls with 0  anyway possible?
0
 
UnifiedISCommented:
use an ISNULL to show 0 instead

SELECT A.CategoryName,
ISNULL(A.ContactServiceID, 0) AS ContactServiceId
FROM (SELECT DISTINCT CategoryName, ContactServiceID FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  ContactCategoriesTable
ON ContactCategoriesTable.CategoryName = A.CategoryName
AND ContactCategoriesTable.ContactServiceID  = 3
0
 
Jason JonesGIS/APP DevAuthor Commented:
UnifiedIS,  the last solution didn't work.. it returned ALL of the records in the database with the ContactServiceID.  

Simon yours worked no problem.

You guys are both great.. thanks for your help today.  I have to head out to a meeting and back in the morning.  don't worry I plan to split the point.
0
 
UnifiedISCommented:
I think the left outer join now needs to include the ContactServiceID value also

SELECT A.CategoryName,
ISNULL(A.ContactServiceID, 0) AS ContactServiceId
FROM (SELECT DISTINCT CategoryName, ContactServiceID FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  ContactCategoriesTable
ON ContactCategoriesTable.CategoryName = A.CategoryName
AND ContactCategroiesTable.ContactServiceID = A.ContactServiceID
AND ContactCategoriesTable.ContactServiceID  = 3
0
 
PortletPaulfreelancerCommented:
UNION simplifies a resultset so there are only distinct rows.

It is therefore not necessary to use SELECT DISTINCT and UNION in the same query

Suggest you remove "distinct" from line 6 of  query at ID: 40645261
0
 
Jason JonesGIS/APP DevAuthor Commented:
That didn't work either UnifiedIS  I got the entire recordset
0
 
UnifiedISCommented:
I'm not sure why it's not working. I built a dummy table with your data and wrote the query below that yields these results as desired.  You should be able to swap out my temp table with your real one to make it work correctly.
blue      3
green      0
red      3
yellow      0

DECLARE @CCT Table (
      ContactServiceID  int,
      CategoryName  varchar(50)
      )
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 1, 'red'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 1, 'blue'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 1, 'green'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 2, 'red'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 2, 'yellow'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 3, 'red'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 3, 'blue'
INSERT INTO @CCT (ContactServiceID, CategoryName)
SELECT 4, 'yellow'


SELECT C.CategoryName,
      ISNULL(NOC.ContactServiceID, 0)
FROM (SELECT DISTINCT CategoryName
            FROM @CCT) C
LEFT OUTER JOIN @CCT NOC
      ON NOC.CategoryName = C.CategoryName
      AND NOC.ContactServiceID = 3
0
 
Jason JonesGIS/APP DevAuthor Commented:
Humm..  the last one you have

SELECT A.CategoryName,
ISNULL(A.ContactServiceID, 0) AS ContactServiceId
FROM (SELECT DISTINCT CategoryName, ContactServiceID FROM ContactCategoriesTable) A
LEFT OUTER JOIN ContactCategoriesTable  ContactCategoriesTable
ON ContactCategoriesTable.CategoryName = A.CategoryName
AND ContactCategoriesTable.ContactServiceID = A.ContactServiceID
AND ContactCategoriesTable.ContactServiceID  = 3

Open in new window


This new one you have

SELECT C.CategoryName,
      ISNULL(NOC.ContactServiceID, 0)
FROM (SELECT DISTINCT CategoryName
            FROM ContactCategoriesTable) C
LEFT OUTER JOIN ContactCategoriesTable NOC
      ON NOC.CategoryName = C.CategoryName
      AND NOC.ContactServiceID = 3

Open in new window


This last one works :)
0
 
Jason JonesGIS/APP DevAuthor Commented:
Great help, thanks guys!
0
 
UnifiedISCommented:
I think I had it right the first time (except for typing the alias wrong)... :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.