SQL is very good at performing joins on data and there are endless ways to optimise the performance when it comes to joining tables and filetring the data when an item is one thing or the other. But how is it possible to take all of the records from one table and some from another?
I was given such a task, to return data from one table that existed in the second table, which was a simple left join. But then there was an additional requirement to return all data from table one that didn’t exist in table to (and vice versa).
Drawing this was a case of a Venn Diagram. The first circle being table one, the second circle being table two. Returning any combination of one, two or three of the parts.
<Image 1: Simple Venn diagram>
Having worked with bitwise fields within Active Directory for an object’s property to be multiple valued within one data field, this was worth looking into. A bitwise field being a decimal value allowing flags to be set to true or false.
- 00001
- 00010
- 00011
Assigning a category to each record depending on the records appearance in either table one, two or both allows filtering using bitwise logic. For example, In table one and two, but not in both, i.e. unassigned records.
<image 2: Venn diagram showing filter>
Taylor's Tutorials 