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.
Rows where columns 5 through 9 are found in Table2, but are not found in Table1 columns 3 through 7.
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)
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.
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.
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.