October 15, 2008 at 2:07 pm
At least for me! I have two tables, t1 and t2 each with two columns
t1: (c1, c2) t2 (d1, d2)
a b e f
c d a b
e f c d
a e j k
b a
I'm trying to write the t-sql to find those rows in t1 that match rows in t2. By match I mean two rows (one from t1, the other from t2) where both c1 = d1 and c2 = d2.
For example, the first row of t1 is the column pair (a, b). We find the same row (with elements in the same order) in t2's second row. So I don't want the query to return (a b) because of the match.
The second row of t1 is (c d) and the third row is (e f), Again t2 has the identical rows with corresponding columns matching. So we don't want the query to return those rows.
The fourth row of t1 is (a e) which doesn't apear at all in t2. We want the query to return that row from t1.
Finally, the fifth row of t1 is (b a). While (a b) appears in t2 that does NOT match this row because the corresponding columns do not match. So we want the query to return (b a) from t1.
I hope the t-sql would generalize easily for matches involving 3 or 4, etc. columns
TIA,
Barkingdog
October 15, 2008 at 2:39 pm
Well the most direct (and general) way is :
SELECT * from t1
EXCEPT
SELECT * from t2
Though it may not always be the fastest way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 2:44 pm
This should be fairly easily accomplished with a couple of outer joins. Please post some table definitions / sample data according to the link in my signature and I'd be happy to whip up a query for you.
October 15, 2008 at 3:11 pm
In re-reading this, it seems you only want to go 1(Rows from T1 not in T2) way, not both ways. That's even easier.
I would do this as
SELECT T1.*
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.a = T2.a and T1.b = T2.b
WHERE T2.b IS NULL
If you wanted to expand the number of columns, you just extend the join. The idea is that it returns the rows where there is no exact match in the second table.
October 15, 2008 at 7:26 pm
Inetresting soluton. I am familiar with the SELECT..EXCEPT SELECT .. approach but I didnt't realize it could be used for more than a single column at a time!
Barkingdog
October 15, 2008 at 8:54 pm
Absooo-rutrey!
Tables are sets of tuples, so all of the SET operators work on tuple-sets.
So EXCEPT and INTERSECT work on multiple columns, just like UNION does.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 9:19 pm
The JOIN solution is also good. Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply