Given the following:
declare @a table
(
pkid int,
value int
)
declare @b table
(
otherID int,
value int
)
insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)
insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)
How do I query for all the values in @a that completely match up with @b?
{@a.pkid = 1, @b.otherID = -1}
would not be returned (only 2 of 3 values match)
{@a.pkid = 2, @b.otherID = -1}
would be returned (3 of 3 values match)
Refactoring tables can be an option.
EDIT: I've had success with the answers from James and Tom H.
When I add another case in @b, they fall a little short.
insert into @b values (-2, 1000)
Assuming this should return two additional rows ({@a.pkid = 1, @b.otherID = -2}
and {@a.pkid = 2, @b.otherID = -2}
, it doesn't work. However, for my project this is not an issue.
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn’t use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows. However, you need to be aware from which table you get the unmatched rows.
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn’t use it.
To match up a row with its previous row, we can enumerate the rows of the UserActivity table and include that enumeration in the two derived tables (rows in LogOn will all have odd row numbers and rows in LogOff will all have even row numbers). We can then join row x in LogOn to row x -1 in LogOff.
You join them together by the column (s) they have in common. The four main types of JOINs are: (INNER) JOIN. LEFT (OUTER) JOIN. RIGHT (OUTER) JOIN. FULL (OUTER) JOIN. When you use a simple (INNER) JOIN, you’ll only get the rows that have matches in both tables. The query will not return unmatched rows in any shape or form.
This is more efficient (it uses TOP 1
instead of COUNT
), and works with (-2, 1000)
:
SELECT *
FROM (
SELECT ab.pkid, ab.otherID,
(
SELECT TOP 1 COALESCE(ai.value, bi.value)
FROM (
SELECT *
FROM @a aii
WHERE aii.pkid = ab.pkid
) ai
FULL OUTER JOIN
(
SELECT *
FROM @b bii
WHERE bii.otherID = ab.otherID
) bi
ON ai.value = bi.value
WHERE ai.pkid IS NULL OR bi.otherID IS NULL
) unmatch
FROM
(
SELECT DISTINCT pkid, otherid
FROM @a a , @b b
) ab
) q
WHERE unmatch IS NOT NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With