Lookup table foreign key null - 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

General SQL Server Forums

New to SQL Server Programming

Lookup table foreign key null

Author

Topic

dbwinger
Starting Member

5 Posts

Posted - 2007-05-01 : 09:11:19

I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use:
SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted]
FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE
WHERE (project_id = @project_id) AND
(EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id)
ORDER BY date_requested ,source,type

But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!

Table DDL:
CREATE TABLE [dbo].[EXTERNAL_FUNDING] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[project_id] [int] NOT NULL ,
[date_requested] [datetime] NULL ,
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [int] NULL ,
[amount] [money] NULL ,
[granted] [bit] NULL ,
[date_granted] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[EXTERNAL_FUNDING_TYPE] (
[type] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 09:13:34

[code]
SELECT EXTERNAL_FUNDING.id,
date_requested as [Request Date],
source as [Source/Sponsor],
ISNULL(EXTERNAL_FUNDING_TYPE.type, '') AS [Type],
amount as [Amount],
granted as [Granted]
FROM EXTERNAL_FUNDING LEFT JOIN EXTERNAL_FUNDING_TYPE
ON EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id
WHERE project_id = @project_id
ORDER BY date_requested , source , type
[/code]

dbwinger
Starting Member

5 Posts

Posted - 2007-05-03 : 17:24:56

Thank you khtan. That was what I needed! I guess I still don't have the best grasp of JOINs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources