November 21, 2008 at 5:26 pm
I'm hoping to learn more about when to use a view and the correct to use a view.
Overview:
I use stored procedures to return information to the website for reports to be made out of them. I found that I was constantly accessing the same tables but just filtering the values of the tables differently. I thought this would be a good use of a view. So I made my view and what i've discovered is that it doesn't join the way i thought it would. It's acting more like an inner join as oppose a left join as i've declare in the creation of the view and in my stored procedure where i'm calling it.
More in depth of the situation:
I have a users table, an attribute table, and a users_attribute table. One to many relation so a user may have several attributes. I'm writing a report that will return selected attributes for the user on a single row. What is happening if a user doesn't have an attribute for every single attribute i want then they are excluded from the results when really i would just like to see a null for that cell because they may have other attributes. So a samle SQL would look like this:
SQL Example
SELECT u.UserId, u.UserName, v1.AttributeValue AS Gender, v2.AttributeValue AS FavoriteColor, v3.AttributeValue AS HairColor
FROM Users as u
LEFT JOIN myview AS v1 ON (u.UserId = v1.UserId)
LEFT JOIN myview AS v2 ON (u.UserId = v2.UserId)
LEFT JOIN myview AS v3 ON (u.UserId = v3.UserId)
WHERE v1.AttributeName = 'Gender'
AND v2.AttributeName = 'Color'
AND v3.AttributeName = 'Hair'
So unless a user has attribute in the database for all three values they aren't returned, shouldn't it just list null?
Attributes Table
*AttributeId
AttributeName
Users_Attributes Table
*UAId
fk-AttributeId
fk-UserId
AttributeValue
--Note: A user can have multiple values for the same attribute
November 24, 2008 at 5:20 am
In the WHERE clause of your query you define AND operator. That will show results of it matches results from all three views. If you need the result of every possible match with one of the views you should replace the AND with an OR.
November 24, 2008 at 5:27 am
mblack (11/21/2008)
I'm hoping to learn more about when to use a view and the correct to use a view.Overview:
I use stored procedures to return information to the website for reports to be made out of them. I found that I was constantly accessing the same tables but just filtering the values of the tables differently. I thought this would be a good use of a view. So I made my view and what i've discovered is that it doesn't join the way i thought it would. It's acting more like an inner join as oppose a left join as i've declare in the creation of the view and in my stored procedure where i'm calling it.
More in depth of the situation:
I have a users table, an attribute table, and a users_attribute table. One to many relation so a user may have several attributes. I'm writing a report that will return selected attributes for the user on a single row. What is happening if a user doesn't have an attribute for every single attribute i want then they are excluded from the results when really i would just like to see a null for that cell because they may have other attributes. So a samle SQL would look like this:
SQL Example
SELECT u.UserId, u.UserName, v1.AttributeValue AS Gender, v2.AttributeValue AS FavoriteColor, v3.AttributeValue AS HairColor
FROM Users as u
LEFT JOIN myview AS v1 ON (u.UserId = v1.UserId)
LEFT JOIN myview AS v2 ON (u.UserId = v2.UserId)
LEFT JOIN myview AS v3 ON (u.UserId = v3.UserId)
WHERE v1.AttributeName = 'Gender'
AND v2.AttributeName = 'Color'
AND v3.AttributeName = 'Hair'
So unless a user has attribute in the database for all three values they aren't returned, shouldn't it just list null?
Attributes Table
*AttributeId
AttributeName
Users_Attributes Table
*UAId
fk-AttributeId
fk-UserId
AttributeValue
--Note: A user can have multiple values for the same attribute
there are many useful ways we can use views, but i use views due to
1. views hides the original source tables .
2. they prevents the execution of the complex queries especially that includes joins, as they contains the result sets of the complex queries and we only have to slect and display the columns.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 24, 2008 at 3:28 pm
I tried using the 'OR' but then i get results in my column that shouldn't belong long. Such as hair color i'd get values of male also thrown in there. So I think i need to keep the 'AND'. The problem is that not every user has an entry for every attribute and i need to see those nulls returned if they don't but instead the query omits them from the result. That is why I'm lead to believe the issue is with the JOINs.
If i can't get the joins to work, the guy who posted second gave me the idea of handling a lot of the complexity of the query that i might have to do to get this work correctly in my view then as I need to access this information throughout my other queries that view will come in handy.
November 24, 2008 at 7:30 pm
The problem you ran into has nothing to do with views or the use of views. The problem has to do with your understanding of how outer joins work.
In your query:
SELECT u.UserId, u.UserName, v1.AttributeValue AS Gender, v2.AttributeValue AS FavoriteColor, v3.AttributeValue AS HairColor
FROM Users as u
LEFT JOIN myview AS v1 ON (u.UserId = v1.UserId)
LEFT JOIN myview AS v2 ON (u.UserId = v2.UserId)
LEFT JOIN myview AS v3 ON (u.UserId = v3.UserId)
WHERE v1.AttributeName = 'Gender'
AND v2.AttributeName = 'Color'
AND v3.AttributeName = 'Hair'
You are using an outer join to join to your view multiple times. By using part of the join criteria in the where clause you are forcing the join to the equivalent of an inner join. In other words, you will now only get matches for users that have all three of your attributes.
To get the results that you are looking for, you need to move the criteria into the join clause. An example would be:
SELECT u.UserId, u.UserName, v1.AttributeValue AS Gender, v2.AttributeValue AS FavoriteColor, v3.AttributeValue AS HairColor
FROM Users as u
LEFT JOIN myview AS v1 ON u.UserId = v1.UserId AND v1.AttributeName = 'Gender'
LEFT JOIN myview AS v2 ON u.UserId = v2.UserId AND v2.AttributeName = 'Color'
LEFT JOIN myview AS v3 ON u.UserId = v3.UserId AND v3.AttributeName = 'Hair'
If you want to now include a filter in the where clause, you would need to limit the where clause to columns in the Users table. So, if we wanted to find any users that are Male or Blonde - the where clause would be:
WHERE v1.AttributeValue = 'Male'
OR v3.AttributeValue = 'Blonde'
With this where clause - any Users that do not have the Gender attribute will not be included in the results. If you wanted to see those Users also - because they might also have one of the other attributes you would need to do the following:
WHERE (v1.AttributeValue = 'Male' OR v1.AttributeValue IS NULL)
OR (v3.AttributeValue = 'Blonde' OR v3.AttributeValue IS NULL)
Now, we have the problem of including those Users that don't have either attribute value. So, to eliminate those rows you have to add in the following:
WHERE ((v1.AttributeValue = 'Male' OR v1.AttributeValue IS NULL)
OR (v3.AttributeValue = 'Blonde' OR v3.AttributeValue IS NULL)
AND (v1.AttributeValue IS NOT NULL OR v3.AttributeValue IS NOT NULL))
As you start to add checks for different attribute values this is going to get real ugly, real fast. You would be much better using dynamic SQL for this - since the above constructs probably will not be able to utilize any indexes.
As a final note - I would seriously consider changing the database structure to avoid this type of construct. Using this attribute\value structure is also called the OTLT (One True Lookup Table) - google that and you will be able to see all of the pros\cons of using such a structure.
Good luck...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply