Help with view - too many tables?

[funforus]

I'm sort of new to SQL Server so forgive me.

I've been asked to create a view that requires a table be linked 9 times to the same 'core' table. For example, the core table is tblCustomers, and the tblCodes is linked to 9 different fields in tblCustomers (gender, religion, ethnicity etc. all stored in tblCodes).

My problem is that once I get to the 8th occurence of tblCodes, the view designer starts adding Right Outer Joins and equijoins where there should only be Left Outer Joins. Could there just be too many tables for the view designer to handle?

I know there are probably other problems with this approach, but it's my task at hand.

Thanks,
Jenny

 

[rac2]

Who knows what designer tools are doing or why they go wrong. Why not code it yourself.

SELECT p.name, 
       a.[description] AS "Gender",
       b.[description] AS "Religion",
       c.[description] AS "EthnicChoice"
FROM tblCustomers p
JOIN tblCodes a ON a.code = p.gender_code
               AND a.type = 'gender'
JOIN tblCodes b ON b.code = p.religion_code
               AND b.type = 'religion'
JOIN tblCodes c ON c.code = p.ethnicity_code
               AND c.type = 'ethnic'

If you like this idea, post the names of the columns in the two tables and some example data and we can work it out exactly.

One detail that is needed is whether the codes are unique over all of the different types of customer attributes or the code values are only unique within a type and the tblCodes tables has a column for the type as I have assumed.

An observation about the need for outer joins on code tables. The code table should have all of the possible values of codes. There should not be any codes in the tblCustomers table that are not in the tblCodes table. If this is true then an inner join is sufficient. If it is not true then there is a problem with the application, it should not allow invalid codes to be entered.

 

[funforus]

Thanks for your help. So, I want all the records from tblCustomers, whether or not they have entries in the gender, religion, or ethnicity fields.

*The field names are:
tblCustomers.gender (might have a value of 1004 for example)
tblCustomers.religion
tblCustomers.ethnicity

*Link to the following in all three cases:
tblCodes.tblEntriesID (1004 would be found here)

*The field I need from tblCodes is:
tblCodes.LongDescription (the row with tblEntriesID 1004 would have a the LongDescription "Female" as text)

Hope this makes sense!

Thanks

 

[rac2]

Except for the issue of missing entries here is a query for the VIEW.

SELECT p.name,
       a.[LongDescription] AS "Gender",
       b.[LongDescription] AS "Religion",
       c.[LongDescription] AS "EthnicChoice"
FROM tblCustomers p
JOIN tblCodes a ON a.tblEntriesID = p.gender
JOIN tblCodes b ON b.tblEntriesID = p.religion
JOIN tblCodes c ON c.tblEntriesID = p.ethnicity

There are two ways to handle the missing data in the tblCustomers table.

One, is to add a code and description to tblCodes for the unknowns. Any code will do, the next available is fine, maybe tblEntriesID = 1075 and LongDescription = 'Unknown'. Then update existing records to the value 1075 wherever the demographics are missing. And modify the application to store 1075 for missing data.

The other is to use the ISNULL(column_name, value_to_use_if_null) function to display 'Unknown'.

SELECT p.name,
       ISNULL(p.gender, 'Unknown', a.[LongDescription]) 
         AS "Gender",
       ISNULL(p.religion, 'Unknown', b[LongDescription]) 
         AS "Religion",
       ISNULL(p.ethnicity, 'Unknown', c.[LongDescription]) 
         AS "EthnicChoice"
FROM tblCustomers p
JOIN tblCodes a ON a.tblEntriesID = p.gender
JOIN tblCodes b ON b.tblEntriesID = p.religion
JOIN tblCodes c ON c.tblEntriesID = p.ethnicity

The advantages of the first approach will be that queries invloving tblCustomers will be more simple to write and read. And it will not be necessary to remember to handle the NULL values (or the empty string values '', depending on what is stored for missing data) when writing new queries. For example, in the future you may wish to obtain a breakdown of customers by age, sex, race, etc. If there is an explicit value for Unknown, it will be tallied without any finagling with ISNULL().

I assumed that missing values were stored as NULL where I used ISNULL(); if they are stored as '' then it will be necessary to write a CASE expression instead. Just one more point in favor of 1075 and 'Unknown'.