Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL: How do I get the rows from one table whose values completely match up with values in another table?

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.

like image 614
Austin Salonen Avatar asked Sep 19 '08 17:09

Austin Salonen


People also ask

How do I get non-matching rows in SQL Server?

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.

Does join return non matching rows in SQL?

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.

How do I match up a row with its previous row?

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.

How do you join two tables together in SQL?

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.


1 Answers

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
like image 159
Quassnoi Avatar answered Sep 28 '22 17:09

Quassnoi