Here is a situation. There is a table with elements ( element ), elements can be of a different type ( element_type ), there are properties inherent in elements ( prop ), and each type of element has its own set of properties ( m: n type_has_prop ), the value of the property for a particular element is set in element_prop_value . Also, prop has an is_finite flag, which means that it has a strictly limited, finite number of values ​​(for example, the types of enclosures for the elements), all possible options for each property are fixed in the prop_finite_value table, however, different types of elements are inherent in For types, what property values ​​are inherent in the element_type_has_prop_finite_value table.

EER

Now to the problem, I, obviously, somewhere in the work of the application and there was a nuisance. Suppose in element_prop_value there is a record that element X has a property - a case type with a value of 0805 , it is fixed rigidly, simply written as a charfield, also we have an entry in the table prop_finite_value that the case type property can take such values ​​and there is indeed a value of 0805 , BUT, the problem is that in the table element_type_has_prop_finite_value as a result of the application’s work, the record disappeared somewhere, that for some type of elements there is an option to choose such a body type, and, like me Understood, the case is not one oh, just realized when they didn’t find in the combo box in the interface of the most frequently used body type ... As a result, I want to make such a query to the database that would select all the value values ​​from the element_prop_value table and check if the element type has the corresponding finite properties ... How joins can be connected to such a mess?

  • You have 2 tables named finite . what to look for? - Novitskiy Denis
  • @NovitskiyDenis of cases is that you need to look for the "absence" of a record in the element_type_has_prop_finite_value table, so my brain explodes, if there is an entry in element_prop_value, then there must be a bunch in element_type_has_prop_finite_value - Philip Bondarev

1 answer 1

Based on the scheme, records that are in element_prop_value , but which are not in element_type_has_prop_finite_value are searched for in the following way:

 select e.id_prop, e.id_element, e.value from element_prop_value e left join prop_finite_value p on e.id_prop=p.id_prop left join element_type_has_prop_finite_value et on p.id=et.id_prop_finite_value where et.id_prop_finite_value is null