Understanding outer joins
I think the title is right!
I'm trying to find out what two SELECT statements can get either the MATCHES between two tables and the mismatches between two tables.
Consider the fields in a table, and the forms where those fields may be displayed. There is patently a many-to-many relationship between these two sets.
The presence of a field on a form indicates that the field is, in a sense, a member of the form; that there is a match between that form and that field. Now, another field may not be on the form, so there is a mismatch. However, I can't find an easy way to detect those "missing" fields.
Let us have a table T_FIelds and a table T_Forms. Each table has many attributes, defining fields and forms respectively. Imagine a table R_Field_Forms, where every field on ewvery form gets an entry. This entry can also, patently, have additional attributes (location, color, etc).
Most DBMS that I have seen display this as a list of fields available, a set of four arrows (facing in on of two directions) and a list of fields that are on the form. By selecting an entry in either list you can move either the single field (single-headed arrow), or all the contents in the selected list (double-headed arrow), to the other list. Each field is only ever in one list or the other, and yet I can't find a SQL statement that will give me the fields that are NOT on a form.
The cardinality of fields and forms is typically fairly small, and yet it seems to me that there should be a well-defined method of specifying presence or absence in the relationship. Presence is the regular INNER JOIN between either T_Fields or T_Forms and R_FormFields. ABSENCE looks like it would be the OUER JOIN, but this mechanism requires the presence of an entry (rather than tolerating the absence of an entry).
In procedural code it would be something like this:
Which gives me both the indication of presence or absence, but also the absence to add the FieldName to the appropriate list. How might I do this in SQL?
For each Field
If there exists a record in R_FormFIelds with ThisFieldID and requestyed FormID then