2 tables.. i need to display data - "where x, y not in x, y"

[kev510]

Hello everyone,
sorry about the confusing title.

Here's the problem I'm running into. Table1 has fields X and Y, and table2 also has fields X and Y.

I would like to display all of Table1's data where Table1's X, Y values do not match to Table2's X and Y data. Here's an example -

Table1 Table2
X Y X Y
------- -------
1 2 1 2
3 4 3 4
5 6 9 8

in this case, I would like to be only be returned with the result

Table1
X Y
-------
5 6

Please let me know how this can be done. Thank you!

-Kevin

 

[AlexCuse]

You want to use a left join, and in this join ensure that both columns are equal. THen, take only the values from table1 with corresponding NULLs (no match) in table2.

Hope this helps,

Alex

Oh yeah here is how you can do it with code:

[COLOR=blue]select[/color] table1.x, table1.y
[COLOR=blue]from[/color] table1
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] table2
[COLOR=blue]on[/color] table1.x = table2.x
and table1.y = table2.y
[COLOR=blue]where[/color] table2.x [COLOR=blue]is[/color] null

Ignorance of certain subjects is a great part of wisdom

 

[kev510]

Thank you for that response!! Now I am 4 outts 5 steps there.

Would that work even if table2 had data that table1 didnt?

 

[AlexCuse]

>>>Would that work even if table2 had data that table1 didnt?

No, you'd need a separate query for that. Or if this is for a report you cuold do a union. Like this:

[COLOR=blue]select[/color] [COLOR=red]'Table1'[/color] [COLOR=blue]as[/color] TableName
,table1.x, table1.y
[COLOR=blue]from[/color] table1
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] table2
[COLOR=blue]on[/color] table1.x = table2.x
and table1.y = table2.y
[COLOR=blue]where[/color] table2.x [COLOR=blue]is[/color] null

union all [COLOR=blue]select[/color] [COLOR=red]'Table2'[/color] [COLOR=blue]as[/color] TableName
,table2.x, table2.y
[COLOR=blue]from[/color] table2
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] table1
[COLOR=blue]on[/color] table2.x = table1.x
and table2.y = table1.y
[COLOR=blue]where[/color] table1.x [COLOR=blue]is[/color] null

Notice that the only change in the second part of query (after union all) is the side of the join tables reside on. I added the 'Table1' as TableName so that you can see which table the row in question came from.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom