Select a result when left join only joins on some rows
I need to implement a business validation rule that checks the contents of
2 tables. The rule has 3 parts:
If all the rows in table one match a row in talbe 2 then it should pass
validation
If all of the rows in table one do not match a row in table 2 then it
should pass.
If some of the rows in table 1 join to table 2 and others don't then it
should fail.
I've added three datasets below to show when it should pass and fail
Dataset 1 (this should pass):
Table1
ID Other Column
1 xxxx
2 xxxx
3 xxxx
4 xxxx
Table2
ID FK OtherColumn
1 1 xxxx
2 2 xxxx
3 3 xxxx
4 4 xxxx
Dataset 2 (This should pass):
Table1
ID Other Column
1 xxxx
2 xxxx
3 xxxx
4 xxxx
Table2
ID FK OtherColumn
1 5 xxxx
2 6 xxxx
3 7 xxxx
4 8 xxxx
Dataset 3 (This should fail because ID 2 and 3 in table1 don't match any
FK in Table2):
Table1
ID Other Column
1 xxxx
2 xxxx
3 xxxx
4 xxxx
Table2
ID FK OtherColumn
1 1 xxxx
2 5 xxxx
3 6 xxxx
4 4 xxxx
You can join the two tables by doing the following:
SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.FK
Table2 will have 0 or 1 record for each record in Table1
So far the only way I have worked out how to do this is by doing to
selects and comparing the number of rows:
SELECT COUNT(Table1.ID)
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.FK
And compare with
SELECT COUNT(Table1.ID)
FROM Table1
I think it works, but it feels like there should be an easier way to do this.
If it makes a difference, the database is Microsoft SQL 2008
No comments:
Post a Comment