Join and Null Value Issues - 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 2005 Forums

Transact-SQL (2005)

Join and Null Value Issues

Author

Topic

abransom
Starting Member

2 Posts

Posted - 2009-11-16 : 12:27:27

Basically, what I am trying to do is check a table. If a product exists in the table I want the column to say true, if it doesn't exist in the table on want the column to say false.

I have 2 tables:

Mobility_Solution_Applications

AppID int
CategoryID int
Application nvarchar

Mobility_Solutions

SolutionID int
ProductID int
AppID int

Here is my current query:

SELECT Distinct a.AppID, Application,(Case When ProductID IS Not NULL AND ProductID = 154 Then 'True' Else 'False' End) As [Checked]
FROM Mobility_Solution_Applications a
Left Outer JOIN Mobility_Solutions b
ON a.AppID = b.AppID
WHERE a.CategoryID = 6

Just as an example, if I have a product 154 that is part of category 6, and the product does not exist in the table Mobility_Solutions then it should and does return the following:

AppID Application Checked
7 Turning Automotive Seating False
8 Transfer Seating False

The problem happens when 154 DOES exist in the table and another product has the same AppID. It should return the same as above except one entry should say true. This is what it ends up returning:

7 Turning Automotive Seating False
7 Turning Automotive Seating True
8 Transfer Seating False

I know this is a logic issue, but I am having trouble figuring out the proper syntax.

Help!!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 13:08:11

Try this:


SELECT a.AppID
       ,Application
       ,max(Case When ProductID is not null Then 'True' Else 'False' End) As [Checked]
FROM   Mobility_Solution_Applications a
Left Outer JOIN Mobility_Solutions b
       ON a.AppID = b.AppID
       and b.productid = 154
WHERE a.CategoryID = 6 
group by a.AppID
       ,Application

Be One with the Optimizer
TG

abransom
Starting Member

2 Posts

Posted - 2009-11-16 : 15:57:40

Fantastic! Worked perfectly. Thanks. That one was making my brain bleed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources