View Problem

[Keith]

I have created a view to test some of the data in my database. I am
relatively new to SQL so may have caused this problem by doing something
wrong.

I have a table called SYS_Individual which contains records on individuals.
A number of the fields in this take a numeric entry which corresponds to an
entry in another table (used to populate drop down menus in the application
being developed).

The tables which are used for the dropdowns are all related with one-to-many
to the Individual table.

However, when I run the View below, it returns only the records from the
SYS_Individual table where ALL the fields being referenced in other tables
are filled in - if a field is left empty, the whole record is not returned.

What might cause this, and how could I get around this?

I will post DDL for Table and Diagram if required.

Thank you
SELECT dbo.SYS_Individual.IND_First_Name AS [First Name],
dbo.SYS_Individual.IND_Surname AS Surname, dbo.SYS_DD_Age_Codes.AGE_Category
AS Age,
dbo.SYS_DD_Bank_Codes.BANKS_Institution,
dbo.SYS_DD_Business_Relationship_Codes.BUSRELAT_Ca tegory,
dbo.SYS_DD_Business_Type_Codes.BUSINESSTYPE_Type,
dbo.SYS_DD_Disadvantage_Codes.DIS_Category AS Disadvantage,
dbo.SYS_DD_Economic_Activity_Codes.ECONOMIC_Catego ry,
dbo.SYS_DD_Ethnicity_Codes.ETHNICITY_Category AS Ethnicity,
dbo.SYS_DD_Exit_Codes.EXIT_Category,
dbo.SYS_DD_Gender_Codes.GENDER_Category AS Gender,
dbo.SYS_DD_Growth_Potential_Codes.GROWTH_Potential AS
[Growth Potential],
dbo.SYS_DD_Marital_Status_Codes.MARITAL_Status AS
[Marital Status], dbo.SYS_DD_Referral_Source_Codes.REFERRAL_Source,

dbo.SYS_DD_Start_Up_Confirmation_Codes.STARTCONF_C ategory,
dbo.SYS_DD_Status_Codes.INDSTAT_Status,
dbo.SYS_DD_Training_Codes.TRAINING_Category
FROM dbo.SYS_DD_Age_Codes INNER JOIN
dbo.SYS_Individual ON dbo.SYS_DD_Age_Codes.AGE_ID =
dbo.SYS_Individual.IND_DD_Age_ID INNER JOIN
dbo.SYS_DD_Bank_Codes ON
dbo.SYS_Individual.IND_DD_Bank_ID = dbo.SYS_DD_Bank_Codes.BANKS_ID INNER
JOIN
dbo.SYS_DD_Business_Relationship_Codes ON
dbo.SYS_Individual.IND_DD_Business_Status_ID =
dbo.SYS_DD_Business_Relationship_Codes.BUSRELAT_ID INNER JOIN
dbo.SYS_DD_Business_Type_Codes ON
dbo.SYS_Individual.IND_DD_Potential_Business_Type_ ID =
dbo.SYS_DD_Business_Type_Codes.BUSINESSTYPE_ID INNER JOIN
dbo.SYS_DD_Disadvantage_Codes ON
dbo.SYS_Individual.IND_DD_Disadvantage_ID =
dbo.SYS_DD_Disadvantage_Codes.DIS_ID INNER JOIN
dbo.SYS_DD_Economic_Activity_Codes ON
dbo.SYS_Individual.IND_DD_Economic_Activity_ID =
dbo.SYS_DD_Economic_Activity_Codes.ECONOMIC_ID INNER JOIN
dbo.SYS_DD_Ethnicity_Codes ON
dbo.SYS_Individual.IND_DD_Ethnicity_ID =
dbo.SYS_DD_Ethnicity_Codes.ETHNICITY_ID INNER JOIN
dbo.SYS_DD_Exit_Codes ON
dbo.SYS_Individual.IND_DD_Exit_ID = dbo.SYS_DD_Exit_Codes.EXIT_ID INNER JOIN
dbo.SYS_DD_Gender_Codes ON
dbo.SYS_Individual.IND_DD_Gender_ID = dbo.SYS_DD_Gender_Codes.GENDER_ID
INNER JOIN
dbo.SYS_DD_Growth_Potential_Codes ON
dbo.SYS_Individual.IND_DD_Growth_Potential_ID =
dbo.SYS_DD_Growth_Potential_Codes.GROWTH_ID INNER JOIN
dbo.SYS_DD_Marital_Status_Codes ON
dbo.SYS_Individual.IND_DD_Marital_Status_ID =
dbo.SYS_DD_Marital_Status_Codes.MARITAL_ID INNER JOIN
dbo.SYS_DD_Referral_Source_Codes ON
dbo.SYS_Individual.IND_DD_Referal_Source_Code =
dbo.SYS_DD_Referral_Source_Codes.REFERRAL_ID INNER JOIN
dbo.SYS_DD_Start_Up_Confirmation_Codes ON
dbo.SYS_Individual.IND_DD_Start_Up_Confirmation_ID =
dbo.SYS_DD_Start_Up_Confirmation_Codes.STARTCONF_I D INNER JOIN
dbo.SYS_DD_Status_Codes ON
dbo.SYS_Individual.IND_DD_Status_ID = dbo.SYS_DD_Status_Codes.INDSTAT_ID
INNER JOIN
dbo.SYS_DD_Training_Codes ON
dbo.SYS_Individual.IND_DD_Training_Status_ID =
dbo.SYS_DD_Training_Codes.TRAINING_ID

[Jacco Schalkwijk]

If you want rows even when not all columns referring other tables are not
NULL (or not filled in), you have to change (some of) your INNER JOINs to
LEFT OUTER JOINs.

--
Jacco Schalkwijk
SQL Server MVP
"Keith" <@.> wrote in message
news:Eo*********************@wards.force9.net...

I have created a view to test some of the data in my database. I am
relatively new to SQL so may have caused this problem by doing something
wrong.

I have a table called SYS_Individual which contains records on individuals. A number of the fields in this take a numeric entry which corresponds to an entry in another table (used to populate drop down menus in the application being developed).

The tables which are used for the dropdowns are all related with one-to-many to the Individual table.

However, when I run the View below, it returns only the records from the
SYS_Individual table where ALL the fields being referenced in other tables
are filled in - if a field is left empty, the whole record is not returned.
What might cause this, and how could I get around this?

I will post DDL for Table and Diagram if required.

Thank you
SELECT dbo.SYS_Individual.IND_First_Name AS [First Name],
dbo.SYS_Individual.IND_Surname AS Surname, dbo.SYS_DD_Age_Codes.AGE_Category AS Age,
dbo.SYS_DD_Bank_Codes.BANKS_Institution,
dbo.SYS_DD_Business_Relationship_Codes.BUSRELAT_Ca tegory,
dbo.SYS_DD_Business_Type_Codes.BUSINESSTYPE_Type,
dbo.SYS_DD_Disadvantage_Codes.DIS_Category AS Disadvantage,
dbo.SYS_DD_Economic_Activity_Codes.ECONOMIC_Catego ry, dbo.SYS_DD_Ethnicity_Codes.ETHNICITY_Category AS Ethnicity,
dbo.SYS_DD_Exit_Codes.EXIT_Category,
dbo.SYS_DD_Gender_Codes.GENDER_Category AS Gender,
dbo.SYS_DD_Growth_Potential_Codes.GROWTH_Potential AS [Growth Potential],
dbo.SYS_DD_Marital_Status_Codes.MARITAL_Status AS
[Marital Status], dbo.SYS_DD_Referral_Source_Codes.REFERRAL_Source,

dbo.SYS_DD_Start_Up_Confirmation_Codes.STARTCONF_C ategory,
dbo.SYS_DD_Status_Codes.INDSTAT_Status,
dbo.SYS_DD_Training_Codes.TRAINING_Category
FROM dbo.SYS_DD_Age_Codes INNER JOIN
dbo.SYS_Individual ON dbo.SYS_DD_Age_Codes.AGE_ID =
dbo.SYS_Individual.IND_DD_Age_ID INNER JOIN
dbo.SYS_DD_Bank_Codes ON
dbo.SYS_Individual.IND_DD_Bank_ID = dbo.SYS_DD_Bank_Codes.BANKS_ID INNER
JOIN
dbo.SYS_DD_Business_Relationship_Codes ON
dbo.SYS_Individual.IND_DD_Business_Status_ID =
dbo.SYS_DD_Business_Relationship_Codes.BUSRELAT_ID INNER JOIN
dbo.SYS_DD_Business_Type_Codes ON
dbo.SYS_Individual.IND_DD_Potential_Business_Type_ ID = dbo.SYS_DD_Business_Type_Codes.BUSINESSTYPE_ID INNER JOIN
dbo.SYS_DD_Disadvantage_Codes ON
dbo.SYS_Individual.IND_DD_Disadvantage_ID =
dbo.SYS_DD_Disadvantage_Codes.DIS_ID INNER JOIN
dbo.SYS_DD_Economic_Activity_Codes ON
dbo.SYS_Individual.IND_DD_Economic_Activity_ID =
dbo.SYS_DD_Economic_Activity_Codes.ECONOMIC_ID INNER JOIN
dbo.SYS_DD_Ethnicity_Codes ON
dbo.SYS_Individual.IND_DD_Ethnicity_ID =
dbo.SYS_DD_Ethnicity_Codes.ETHNICITY_ID INNER JOIN
dbo.SYS_DD_Exit_Codes ON
dbo.SYS_Individual.IND_DD_Exit_ID = dbo.SYS_DD_Exit_Codes.EXIT_ID INNER JOIN dbo.SYS_DD_Gender_Codes ON
dbo.SYS_Individual.IND_DD_Gender_ID = dbo.SYS_DD_Gender_Codes.GENDER_ID
INNER JOIN
dbo.SYS_DD_Growth_Potential_Codes ON
dbo.SYS_Individual.IND_DD_Growth_Potential_ID =
dbo.SYS_DD_Growth_Potential_Codes.GROWTH_ID INNER JOIN
dbo.SYS_DD_Marital_Status_Codes ON
dbo.SYS_Individual.IND_DD_Marital_Status_ID =
dbo.SYS_DD_Marital_Status_Codes.MARITAL_ID INNER JOIN
dbo.SYS_DD_Referral_Source_Codes ON
dbo.SYS_Individual.IND_DD_Referal_Source_Code =
dbo.SYS_DD_Referral_Source_Codes.REFERRAL_ID INNER JOIN
dbo.SYS_DD_Start_Up_Confirmation_Codes ON
dbo.SYS_Individual.IND_DD_Start_Up_Confirmation_ID =
dbo.SYS_DD_Start_Up_Confirmation_Codes.STARTCONF_I D INNER JOIN
dbo.SYS_DD_Status_Codes ON
dbo.SYS_Individual.IND_DD_Status_ID = dbo.SYS_DD_Status_Codes.INDSTAT_ID
INNER JOIN
dbo.SYS_DD_Training_Codes ON
dbo.SYS_Individual.IND_DD_Training_Status_ID =
dbo.SYS_DD_Training_Codes.TRAINING_ID