> ----- Original Message ----- > From: "Kurt Welgehausen" <[EMAIL PROTECTED]> > > No one can really evaluate this without knowing your view > > definition. > > Here you are. I'm still not sure that it is relavent though. > > CREATE VIEW Device_Property_List AS > SELECT > D.device_id AS device_id, > DK.device_kind_name AS device_kind_name, > DP.device_property_id AS device_property_id, > DP.device_property_name AS device_property_name, > CASE > WHEN DPV.device_property_value NOTNULL THEN > DPV.device_property_value > WHEN T.device_id NOTNULL THEN > CASE > WHEN TPV.device_property_value NOTNULL THEN > TPV.device_property_value > ELSE > TP.device_property_default > END > ELSE > DP.device_property_default > END AS device_property_value > FROM > Device AS D > LEFT JOIN Device_Kind AS DK > ON (D.device_kind_id = DK.device_kind_id) > LEFT JOIN Device_Property AS DP > ON (D.device_kind_id = DP.device_kind_id) > LEFT JOIN Device_Property_Value AS DPV > ON ((D.device_id = DPV.device_id) > AND (DP.device_property_id = DPV.device_property_id)) > LEFT JOIN Device_Property AS DTP > ON ((D.device_kind_id = DTP.device_kind_id) > AND (DTP.device_property_name = 'Template')) > LEFT JOIN Device_Property_Value AS TID > ON ((D.device_id = TID.device_id) > AND (DTP.device_property_id = TID.device_property_id)) > LEFT JOIN Device AS T > ON (TID.device_property_value = T.device_id) > LEFT JOIN Device_Property AS TP > ON ((T.device_kind_id = TP.device_kind_id) > AND (DP.device_property_name = TP.device_property_name)) > LEFT JOIN Device_Property_Value AS TPV > ON ((T.device_id = TPV.device_id) > AND (TP.device_property_id = TPV.device_property_id)) > ;
Kurt, it turns out that you were right. The problem with my select statements returning unexpected results can be corrected by changing the view's select statement. Changing the first two joins from LEFT OUTER JOINS to normal INNER JOINS clears up the problem. These outer joins produce null extended rows for the devices that don't match the ON conditions. The inner joins omit these rows. It seems to me that this still indicates a bug in SQLite though. Since the WHERE clause is supposed to be applied to the table that results from all the joins in the FROM clause. The fact that some of these rows were produced by outer joins that placed NULL values in the device_property_id column shouldn't make them pass the WHERE device_property_id = 26 condition in the select statement. Does this seem like a bug to you (or anyone else)? --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
