Select more if value is not NULL

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

Transact-SQL (2000)

Select more if value is not NULL

Author

Topic

Frances
Starting Member

20 Posts

Posted - 2005-03-22 : 03:44:10

Hey all,

how can I select extra information if a value is not null? Example:

I have a table which contains MenuItemID's (foreign keys). Some of them are null, some aren't.
If they're not null, I want to retrieve the MenuItem name from the MenuItem-table.
If they're null, I just want to return an empty string.

I've tried using an IF-ELSE statement in my FROM-clause (working with INNER JOINS), but SQL-server doesn't like that...

Any ideas?

Thank you

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-22 : 03:56:38

Can you try like this:

If Exists(Select 'x' From MenuItem Where MenuItemID = @MenuItemID)
Select MenuItemName From MenuItem Where MenuItemID = @MenuItemID
Else
Select '' As MenuItemName

:) While we stop to think, we often miss our opportunity :)

Frances
Starting Member

20 Posts

Posted - 2005-03-22 : 04:50:22

I don't really know how to integrate that in my query. My query is:

SELECT     	dbo.Cms_Banner.FK_SiteID AS SiteID, 
		dbo.Cms_Zone.ZoneID, 
		dbo.Cms_BannerModule.BannerOrder AS ItemOrder, 
                dbo.Cms_Zone.Name AS ZoneName,
		dbo.Cms_BannerModule.FK_MenuItemID AS MenuItemID, 
		dbo.Cms_MenuItem.Name AS MenuItemName		

FROM         	dbo.Cms_Banner INNER JOIN
                dbo.Cms_BannerModule ON dbo.Cms_Banner.BannerID = dbo.Cms_BannerModule.FK_BannerID INNER JOIN
                dbo.Cms_Zone ON dbo.Cms_BannerModule.FK_ZoneID = dbo.Cms_Zone.ZoneID INNER JOIN
		dbo.Cms_MenuItem ON dbo.Cms_MenuItem.MenuItemID = dbo.Cms_BannerModule.FK_MenuItemID
WHERE     	dbo.Cms_Banner.BannerID = @bannerID

This displays the locations that have a FK_MenuItemID, but not the ones that don't.

Thank you

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-03-22 : 04:55:26

Hi
Try this:

SELECT dbo.Cms_Banner.FK_SiteID AS SiteID,
dbo.Cms_Zone.ZoneID,
dbo.Cms_BannerModule.BannerOrder AS ItemOrder,
dbo.Cms_Zone.Name AS ZoneName,
dbo.Cms_BannerModule.FK_MenuItemID AS MenuItemID,
COALESCE(dbo.Cms_MenuItem.Name, '') AS MenuItemName

FROM dbo.Cms_Banner INNER JOIN
dbo.Cms_BannerModule ON dbo.Cms_Banner.BannerID = dbo.Cms_BannerModule.FK_BannerID INNER JOIN
dbo.Cms_Zone ON dbo.Cms_BannerModule.FK_ZoneID = dbo.Cms_Zone.ZoneID LEFT JOIN
dbo.Cms_MenuItem ON dbo.Cms_MenuItem.MenuItemID = dbo.Cms_BannerModule.FK_MenuItemID
WHERE dbo.Cms_Banner.BannerID = @bannerID

Mark

Frances
Starting Member

20 Posts

Posted - 2005-03-22 : 05:00:12

Thanks very much, it works!

Thank you

DClayton77
Starting Member

11 Posts

Posted - 2005-03-22 : 16:13:00

Couldn't you just do a left outer join from the table containing the foreign keys to the table where the menu descriptions are stored?

This would return all results, even the ones with null values. You could then simply use the IsNull function around the menu description field in the select list to replace the null value with a zero length string?

Seems a bit simpler

You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-03-23 : 06:48:44

Am I missing something!?

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources