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_Individ ual.IND_First_N ame AS [First Name],
dbo.SYS_Individ ual.IND_Surname AS Surname, dbo.SYS_DD_Age_ Codes.AGE_Categ ory
AS Age,
dbo.SYS_DD_Bank _Codes.BANKS_In stitution,
dbo.SYS_DD_Busi ness_Relationsh ip_Codes.BUSREL AT_Category,
dbo.SYS_DD_Busi ness_Type_Codes .BUSINESSTYPE_T ype,
dbo.SYS_DD_Disa dvantage_Codes. DIS_Category AS Disadvantage,
dbo.SYS_DD_Econ omic_Activity_C odes.ECONOMIC_C ategory,
dbo.SYS_DD_Ethn icity_Codes.ETH NICITY_Category AS Ethnicity,
dbo.SYS_DD_Exit _Codes.EXIT_Cat egory,
dbo.SYS_DD_Gend er_Codes.GENDER _Category AS Gender,
dbo.SYS_DD_Grow th_Potential_Co des.GROWTH_Pote ntial AS
[Growth Potential],
dbo.SYS_DD_Mari tal_Status_Code s.MARITAL_Statu s AS
[Marital Status], dbo.SYS_DD_Refe rral_Source_Cod es.REFERRAL_Sou rce,

dbo.SYS_DD_Star t_Up_Confirmati on_Codes.STARTC ONF_Category,
dbo.SYS_DD_Stat us_Codes.INDSTA T_Status,
dbo.SYS_DD_Trai ning_Codes.TRAI NING_Category
FROM dbo.SYS_DD_Age_ Codes INNER JOIN
dbo.SYS_Individ ual ON dbo.SYS_DD_Age_ Codes.AGE_ID =
dbo.SYS_Individ ual.IND_DD_Age_ ID INNER JOIN
dbo.SYS_DD_Bank _Codes ON
dbo.SYS_Individ ual.IND_DD_Bank _ID = dbo.SYS_DD_Bank _Codes.BANKS_ID INNER
JOIN
dbo.SYS_DD_Busi ness_Relationsh ip_Codes ON
dbo.SYS_Individ ual.IND_DD_Busi ness_Status_ID =
dbo.SYS_DD_Busi ness_Relationsh ip_Codes.BUSREL AT_ID INNER JOIN
dbo.SYS_DD_Busi ness_Type_Codes ON
dbo.SYS_Individ ual.IND_DD_Pote ntial_Business_ Type_ID =
dbo.SYS_DD_Busi ness_Type_Codes .BUSINESSTYPE_I D INNER JOIN
dbo.SYS_DD_Disa dvantage_Codes ON
dbo.SYS_Individ ual.IND_DD_Disa dvantage_ID =
dbo.SYS_DD_Disa dvantage_Codes. DIS_ID INNER JOIN
dbo.SYS_DD_Econ omic_Activity_C odes ON
dbo.SYS_Individ ual.IND_DD_Econ omic_Activity_I D =
dbo.SYS_DD_Econ omic_Activity_C odes.ECONOMIC_I D INNER JOIN
dbo.SYS_DD_Ethn icity_Codes ON
dbo.SYS_Individ ual.IND_DD_Ethn icity_ID =
dbo.SYS_DD_Ethn icity_Codes.ETH NICITY_ID INNER JOIN
dbo.SYS_DD_Exit _Codes ON
dbo.SYS_Individ ual.IND_DD_Exit _ID = dbo.SYS_DD_Exit _Codes.EXIT_ID INNER JOIN
dbo.SYS_DD_Gend er_Codes ON
dbo.SYS_Individ ual.IND_DD_Gend er_ID = dbo.SYS_DD_Gend er_Codes.GENDER _ID
INNER JOIN
dbo.SYS_DD_Grow th_Potential_Co des ON
dbo.SYS_Individ ual.IND_DD_Grow th_Potential_ID =
dbo.SYS_DD_Grow th_Potential_Co des.GROWTH_ID INNER JOIN
dbo.SYS_DD_Mari tal_Status_Code s ON
dbo.SYS_Individ ual.IND_DD_Mari tal_Status_ID =
dbo.SYS_DD_Mari tal_Status_Code s.MARITAL_ID INNER JOIN
dbo.SYS_DD_Refe rral_Source_Cod es ON
dbo.SYS_Individ ual.IND_DD_Refe ral_Source_Code =
dbo.SYS_DD_Refe rral_Source_Cod es.REFERRAL_ID INNER JOIN
dbo.SYS_DD_Star t_Up_Confirmati on_Codes ON
dbo.SYS_Individ ual.IND_DD_Star t_Up_Confirmati on_ID =
dbo.SYS_DD_Star t_Up_Confirmati on_Codes.STARTC ONF_ID INNER JOIN
dbo.SYS_DD_Stat us_Codes ON
dbo.SYS_Individ ual.IND_DD_Stat us_ID = dbo.SYS_DD_Stat us_Codes.INDSTA T_ID
INNER JOIN
dbo.SYS_DD_Trai ning_Codes ON
dbo.SYS_Individ ual.IND_DD_Trai ning_Status_ID =
dbo.SYS_DD_Trai ning_Codes.TRAI NING_ID

[Jacco Schalkwijk]

Re: View Problem

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:Eoqhc.3506 3$Y%6.4574727@w ards.force9.net ...[color=blue]
> 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[/color]
individuals.[color=blue]
> A number of the fields in this take a numeric entry which corresponds to[/color]
an[color=blue]
> entry in another table (used to populate drop down menus in the[/color]
application[color=blue]
> being developed).
>
> The tables which are used for the dropdowns are all related with[/color]
one-to-many[color=blue]
> 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[/color]
returned.[color=blue]
>
> 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_Individ ual.IND_First_N ame AS [First Name],
> dbo.SYS_Individ ual.IND_Surname AS Surname,[/color]
dbo.SYS_DD_Age_ Codes.AGE_Categ ory[color=blue]
> AS Age,
> dbo.SYS_DD_Bank _Codes.BANKS_In stitution,
> dbo.SYS_DD_Busi ness_Relationsh ip_Codes.BUSREL AT_Category,
> dbo.SYS_DD_Busi ness_Type_Codes .BUSINESSTYPE_T ype,
> dbo.SYS_DD_Disa dvantage_Codes. DIS_Category AS Disadvantage,
>[/color]
dbo.SYS_DD_Econ omic_Activity_C odes.ECONOMIC_C ategory,[color=blue]
> dbo.SYS_DD_Ethn icity_Codes.ETH NICITY_Category AS Ethnicity,
> dbo.SYS_DD_Exit _Codes.EXIT_Cat egory,
> dbo.SYS_DD_Gend er_Codes.GENDER _Category AS Gender,
> dbo.SYS_DD_Grow th_Potential_Co des.GROWTH_Pote ntial[/color]
AS[color=blue]
> [Growth Potential],
> dbo.SYS_DD_Mari tal_Status_Code s.MARITAL_Statu s AS
> [Marital Status], dbo.SYS_DD_Refe rral_Source_Cod es.REFERRAL_Sou rce,
>
> dbo.SYS_DD_Star t_Up_Confirmati on_Codes.STARTC ONF_Category,
> dbo.SYS_DD_Stat us_Codes.INDSTA T_Status,
> dbo.SYS_DD_Trai ning_Codes.TRAI NING_Category
> FROM dbo.SYS_DD_Age_ Codes INNER JOIN
> dbo.SYS_Individ ual ON dbo.SYS_DD_Age_ Codes.AGE_ID =
> dbo.SYS_Individ ual.IND_DD_Age_ ID INNER JOIN
> dbo.SYS_DD_Bank _Codes ON
> dbo.SYS_Individ ual.IND_DD_Bank _ID = dbo.SYS_DD_Bank _Codes.BANKS_ID INNER
> JOIN
> dbo.SYS_DD_Busi ness_Relationsh ip_Codes ON
> dbo.SYS_Individ ual.IND_DD_Busi ness_Status_ID =
> dbo.SYS_DD_Busi ness_Relationsh ip_Codes.BUSREL AT_ID INNER JOIN
> dbo.SYS_DD_Busi ness_Type_Codes ON
> dbo.SYS_Individ ual.IND_DD_Pote ntial_Business_ Type_ID[/color]
=[color=blue]
> dbo.SYS_DD_Busi ness_Type_Codes .BUSINESSTYPE_I D INNER JOIN
> dbo.SYS_DD_Disa dvantage_Codes ON
> dbo.SYS_Individ ual.IND_DD_Disa dvantage_ID =
> dbo.SYS_DD_Disa dvantage_Codes. DIS_ID INNER JOIN
> dbo.SYS_DD_Econ omic_Activity_C odes ON
> dbo.SYS_Individ ual.IND_DD_Econ omic_Activity_I D =
> dbo.SYS_DD_Econ omic_Activity_C odes.ECONOMIC_I D INNER JOIN
> dbo.SYS_DD_Ethn icity_Codes ON
> dbo.SYS_Individ ual.IND_DD_Ethn icity_ID =
> dbo.SYS_DD_Ethn icity_Codes.ETH NICITY_ID INNER JOIN
> dbo.SYS_DD_Exit _Codes ON
> dbo.SYS_Individ ual.IND_DD_Exit _ID = dbo.SYS_DD_Exit _Codes.EXIT_ID INNER[/color]
JOIN[color=blue]
> dbo.SYS_DD_Gend er_Codes ON
> dbo.SYS_Individ ual.IND_DD_Gend er_ID = dbo.SYS_DD_Gend er_Codes.GENDER _ID
> INNER JOIN
> dbo.SYS_DD_Grow th_Potential_Co des ON
> dbo.SYS_Individ ual.IND_DD_Grow th_Potential_ID =
> dbo.SYS_DD_Grow th_Potential_Co des.GROWTH_ID INNER JOIN
> dbo.SYS_DD_Mari tal_Status_Code s ON
> dbo.SYS_Individ ual.IND_DD_Mari tal_Status_ID =
> dbo.SYS_DD_Mari tal_Status_Code s.MARITAL_ID INNER JOIN
> dbo.SYS_DD_Refe rral_Source_Cod es ON
> dbo.SYS_Individ ual.IND_DD_Refe ral_Source_Code =
> dbo.SYS_DD_Refe rral_Source_Cod es.REFERRAL_ID INNER JOIN
> dbo.SYS_DD_Star t_Up_Confirmati on_Codes ON
> dbo.SYS_Individ ual.IND_DD_Star t_Up_Confirmati on_ID =
> dbo.SYS_DD_Star t_Up_Confirmati on_Codes.STARTC ONF_ID INNER JOIN
> dbo.SYS_DD_Stat us_Codes ON
> dbo.SYS_Individ ual.IND_DD_Stat us_ID = dbo.SYS_DD_Stat us_Codes.INDSTA T_ID
> INNER JOIN
> dbo.SYS_DD_Trai ning_Codes ON
> dbo.SYS_Individ ual.IND_DD_Trai ning_Status_ID =
> dbo.SYS_DD_Trai ning_Codes.TRAI NING_ID
>
>[/color]