SQLTeam.com | Weblogs | Forums

Queries to Compare 2 tables

I need help creating queries which compare 2 tables to get the 3 things listed below. Table1 has 18 columns and Table2 has 10 columns.

  1. Rows where columns 5 through 9 are found in Table2, but are not found in Table1 columns 3 through 7.

  2. Rows that were found in Table1 where Column 14 = '3'. Out of those values, which rows exist in Table2 (again, comparing columns 5 through 9 in Table1 vs 3 through 7 in Table2)

  3. Rows that were found in Table1 where Column 14 = '2'. Out of those values, which rows exist in Table2 (again, comparing columns 5-9 in Table1 and 3-7 in Table2).

If it helps, the column names I 'care about' in these tables are:
Table1: FirstName, LastName, UserName, UserID, LoginUserID, Count, Reason (Reason is 'column 14' listed in my questions above)
Table 2: FirstName, LastName, UserName, UserID, LoginUserID, Count

I can provide any additional information needed for this help and thank you in advance.

It would help if you could provide sample data - for example, create table statements for each table - with sample data for each scenario and expected results.

If I understand correctly, you are looking to relate the tables based on multiple columns. If that is the case:

Scenario 1:

Select ...
  From dbo.Table1          t1
  Left Join dbo.Table2     t2 On t2.col3 = t1.col5
                             And t2.col4 = t1.col6
                             And t2.col5 = t1.col7
                             And t2.col6 = t1.col8
                             And t2.col7 = t1.col9
 Where t2.col1 Is Null;

Scenario 2:

Select ...
  From dbo.Table1          t1
 Inner Join dbo.Table2     t2 On t2.col3 = t1.col5
                             And t2.col4 = t1.col6
                             And t2.col5 = t1.col7
                             And t2.col6 = t1.col8
                             And t2.col7 = t1.col9
 Where t1.col14 = 3;

Scenario 3:

Select ...
  From dbo.Table1          t1
 Inner Join dbo.Table2     t2 On t2.col3 = t1.col5
                             And t2.col4 = t1.col6
                             And t2.col5 = t1.col7
                             And t2.col6 = t1.col8
                             And t2.col7 = t1.col9
 Where t1.col14 = 2;

For scenario 1 - if any one of the columns don't match exactly then the row in table1 will be returned. For the other scenarios - only those rows that match exactly on all 5 columns will be returned.

Thank you very much Jeff. I think that is pointing me in the right direction, but I'm still having an issue creating my own queries.

Here is some sample data of the 2 tables and the results I would expect for each scenario.

Table 1:

ID TypeID FirstName LastName UserName UserID LoginID Count Action
10 1 Jim Miller Jmiller j.miller j.miller01 7 3
11 1 John Smith Jsmith j.smith j.smith01 2 3
12 1 Jane Doe Jdoe j.doe j.doe01 5 2

Table 2:

ID TypeID FirstName LastName UserName UserID LoginID Count
1 1 John Smith Jsmith j.smith j.smith01 8
2 2 Jim Miller Jmiller j.miller j.miller01 2
3 1 Jane Doe Jdoe j.doe j.doe01 3
4 2 Bob Jones Bjones b.jones b.jones01 4
5 1 James Jones Jjones j.jones j.jones01 5

Scenario 1:
Bob Jones and James Jones were found in table 2 but not in table 1. So they should show on the output of this query.

Scenario 2:
Jim Miller and Jon Smith were found in table 1 with action = '3'. These records showed again in table 2, so they should show in the output for scenario 2

Scenario 3:
Jane Doe was found in table 1 with action = '2'. This record appeared in table 2, so this record should show on the output, as well as the difference (2) between the "count" columns.

I do want to note, there will be scenarios where First, Last, User, and UserID are same but LoginID are different, for example, and so that is why all fields need to match.

Let me know if this helps your understanding.

For scenario 1 - flip the tables. Look at table2 - join to table1 and return rows where table1.ID is null.

Select ...
  From dbo.Table2          t2
  Left Join dbo.Table1     t1 On t1.FirstName = t2.FirstName
                             And t1.LastName = t2.LastName
                             And t1.UserName = t2.UserName
                             And t1.UserID = t2.UserID
                             And t1.LoginID = t2.LoginID
 Where t1.ID Is Null;

If you want a tested solution you need to provide the tables as create table statements and the data as insert statements (you can use temp tables or table variables for the sample tables).

Declare @action int = 3;

Select ...
  From dbo.Table1          t1
 Inner Join dbo.Table2     t2 On t2.FirstName = t1.FirstName
                             And t2.LastName = t1.LastName
                             And t2.UserName = t1.UserName
                             And t2.UserID = t1.UserID
                             And t2.LoginID = t1.LoginID
 Where t1.Action = @action;

This one query can be used for either scenario - just change the @action variable to the action you want.

1 Like

Where are you getting the "ID" field from? The tables in my database don't actually have that field, I think it was added when I copied data into my post as a table.

From your sample data - that is why I keep asking for sample data in the form of create statements and insert statements. It doesn't actually matter though - you just need to reference a column from the table in the outer join, in this case that is table1.

1 Like

I was able to figure out what I needed. Thanks for the help!

You are welcome - happy to help.