Outer Join Problem

Because we have a lot of different kinds of data that we track, the system that I use uses an Entity-Attribute-Value data model. (Think of the entity id being the row, the attribute id being the column, and the value being a cell value.)

I want to be able to get all the values for an entity in one select. Unfortunately, most can be null, in which case we don't store a row for that value at all in the values table. So I'd like to get everything with an outer join. Unfortunately, that doesn't work very well.

This SQL behaves like you'd expect.

SELECT Event.Event_ID, String_Value.Value, String_Value.Attribute_ID

FROM Event LEFT OUTER JOIN

String_Value ON Event.Event_ID = String_Value.Event_ID

WHERE (Event.Event_ID = 35632)

(It returns 1 row.)

If I add a constraint to the String_Value table, all of a sudden, no rows get returned.

SELECT Event.Event_ID, String_Value.Value, String_Value.Attribute_ID

FROM Event LEFT OUTER JOIN

String_Value ON Event.Event_ID = String_Value.Event_ID

WHERE (Event.Event_ID = 35632) AND (String_Value.Attribute_ID = 69)

How can I revise this to always get the Event ID, but only get string values where the Attribute ID is 69? (This is extremely simplified. What I really want to do involves 7 left outer joins.)

I could do this using a temp table, doing updates for each value, but I'd like to keep things simple, if possible.

Try this

SELECT Event.Event_ID, String_Value.Value, String_Value.Attribute_ID

FROM Event LEFT OUTER JOIN

String_Value ON Event.Event_ID = String_Value.Event_ID
AND (String_Value.Attribute_ID = 69)

WHERE (Event.Event_ID = 35632)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Criteria on the WHERE clause have to be true for the data set, criteria on the JOIN condition only have to be true when rows exist on the join table.

Thats why you should put your criteria on the join clause