Comparing the rows of two queries with NULL values

[Skip821]

So I have two queries with multiple rows and I'm trying to compare the rows from TablesCells to TablesTemplate to make sure their columns are the same. Occasionally TablesCells may have an input where in Templates its null and vice versa.

I was hoping that the TableBuster query would show the Table name, index number, and the cell that is having an issue. But it seems that its comparing each row from TableCells to the every row in TablesTemplates.


My SQL code
TablesCells
SELECT ALG_OPAL_TBL_MASTER_VIEW.GROUP_NAME, ALG_OPAL_TBL_MASTER_VIEW.TABLE_NAME, ALG_OPAL_TBL_VIEW.INDEX_NO, ALG_OPAL_TBL_VIEW.C1, ALG_OPAL_TBL_VIEW.C2, ALG_OPAL_TBL_VIEW.C3, ALG_OPAL_TBL_VIEW.C4, ALG_OPAL_TBL_VIEW.C5, ALG_OPAL_TBL_VIEW.C6, ALG_OPAL_TBL_VIEW.C7, ALG_OPAL_TBL_VIEW.C8, ALG_OPAL_TBL_VIEW.C9, ALG_OPAL_TBL_VIEW.C10
FROM ALG_OPAL_TBL_MASTER_VIEW INNER JOIN ALG_OPAL_TBL_VIEW ON ALG_OPAL_TBL_MASTER_VIEW.TABLE_INDEX = ALG_OPAL_TBL_VIEW.TABLE_INDEX
WHERE (((ALG_OPAL_TBL_MASTER_VIEW.GROUP_NAME)=[]) AND ((ALG_OPAL_TBL_MASTER_VIEW.TABLE_NAME) Like "*Man_W*"));


TemplatesCells
SELECT ALG_OPAL_TBL_MASTER_VIEW.GROUP_NAME, ALG_OPAL_TBL_MASTER_VIEW.TABLE_NAME, ALG_OPAL_TBL_VIEW.INDEX_NO, ALG_OPAL_TBL_VIEW.C1, ALG_OPAL_TBL_VIEW.C2, ALG_OPAL_TBL_VIEW.C3, ALG_OPAL_TBL_VIEW.C4, ALG_OPAL_TBL_VIEW.C5, ALG_OPAL_TBL_VIEW.C6, ALG_OPAL_TBL_VIEW.C7, ALG_OPAL_TBL_VIEW.C8, ALG_OPAL_TBL_VIEW.C9, ALG_OPAL_TBL_VIEW.C10
FROM ALG_OPAL_TBL_MASTER_VIEW INNER JOIN ALG_OPAL_TBL_VIEW ON ALG_OPAL_TBL_MASTER_VIEW.TABLE_INDEX = ALG_OPAL_TBL_VIEW.TABLE_INDEX
WHERE (((ALG_OPAL_TBL_MASTER_VIEW.GROUP_NAME) Like "Templates") AND ((ALG_OPAL_TBL_MASTER_VIEW.TABLE_NAME) Like "*Per*"));


TableBuster SQL
SELECT [0_TablesCells].TABLE_NAME, [0_TablesCells].INDEX_NO, [0_TablesCells].C1, [0_TablesCells].C2, [0_TablesCells].C3, [0_TablesCells].C4, [0_TablesCells].C5, [0_TablesCells].C6, [0_TablesCells].C7, [0_TablesCells].C8, [0_TablesCells].C9, [0_TablesCells].C10
FROM 0_TablesCells, 0_TemplatesCells
WHERE (((Nz([0_TablesCells].[C1],"Hil"))=NZ([0_TemplatesCells].[C1],"Hi")))
OR (((Nz([0_TablesCells].[C2],"Hi"))=NZ([0_TemplatesCells].[C2],"Hi")))
OR (((Nz([0_TablesCells].[C3],"Hi"))=NZ([0_TemplatesCells].[C3],"Hi")))
OR (((Nz([0_TablesCells].[C4],"Hi"))=NZ([0_TemplatesCells].[C4],"Hi")))
OR (((Nz([0_TablesCells].[C5],"Hi"))=NZ([0_TemplatesCells].[C5],"Hi")));

 

[GanzPopp]

You are using OR in the WHERE statement of your TableBuster query. Now if any of these substatements is true, then the row is included in the results and that, if I understand you correctly, is not what you want. Thus, instead use this:

SELECT [0_TablesCells].TABLE_NAME,  [0_TablesCells].INDEX_NO, [0_TablesCells].C1, 
[0_TablesCells].C2,  [0_TablesCells].C3, [0_TablesCells].C4, [0_TablesCells].C5, 
[0_TablesCells].C6, [0_TablesCells].C7, [0_TablesCells].C8,  [0_TablesCells].C9, 
[0_TablesCells].C10
FROM 0_TablesCells, 0_TemplatesCells
WHERE (((Nz([0_TablesCells].[C1],"Hi"))=NZ([0_TemplatesCells].[C1],"Hi"))) 
AND (((Nz([0_TablesCells].[C2],"Hi"))=NZ([0_TemplatesCells].[C2],"Hi")))
AND (((Nz([0_TablesCells].[C3],"Hi"))=NZ([0_TemplatesCells].[C3],"Hi"))) 
AND (((Nz([0_TablesCells].[C4],"Hi"))=NZ([0_TemplatesCells].[C4],"Hi"))) 
AND (((Nz([0_TablesCells].[C5],"Hi"))=NZ([0_TemplatesCells].[C5],"Hi")))

This will show all rows which are the same. If you want the opposite, just add an ! between WHERE and the first ( .