Join Problem

Hi,

I have 2 tables:

CREATE TABLE [dbo].[TBL_CONDITION](
[CONDITIONID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](500) NULL)

CREATE TABLE [dbo].[TBL_CONDITION_CUSTOMER](
[CCAN] [varchar](10) NOT NULL,
[CONDITIONID] [int] NOT NULL,
[FOLLOW-UP_DATE] [nchar](10) NOT NULL)

Sample data is as follows:

INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of statements')
INSERT INTO [tbl_condition] ([NAME])VALUES('Satisfactory review')
INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of latest interim')

INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',1,'03/09/2007')
INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',2,'04/09/2007')

Inrespective of data in child table ([tbl_condition_customer] ) I want all the rows from tbl_condition - I am using outer join for that but am not getting the required output:

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID
WHERE (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

But I am getting output as that of Inner Join?

What am I missing. I want output as follows:

ConditionId Name CCAN
--
1 Receipt of statements 52410
2 Satisfactory review 52410
3 Receipt of latest interim NULL

Hi JayaC

You could change your select statement to the statement shown below.

Chris

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID AND (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

|||Thanks Chris. That worked :)