Cascaded SELECT for multiple fields

Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
I can do a Cascaded Select on 1 field:
SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
or
SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
and I can rewrite it as a INNER JOIN, no problem. So far so good.
But now what happens when it is _BOTH_ pair of fields I wish to check
whether they exist/not exist in Table 2?
For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
Table2)
I have to write this is a INNER JOIN which will work. At least there is a
solution here.
But consider the 4th case. I am stuffed here regardless of doing Cascaded
Select or Join. You want to say
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FROM
Table2)
And I cannot rewrite this as an inner join because rows are only considered
when both fields, _jointly_ are absent from Table2.
There must be a solution here for multiple fields.
In real life I do have 2 tables. And I can gurantee that fld1 is present on
both tables. But I am interested in listing the pairs of fields (fld1, fld2)
present on one table but absent on the other.
Question1) Can it be done with Cascade Selects? How?
Question2) Can it be done with Joins? How?
Thanks
Stephen HoweThis should work:
SELECT Table1.fld1, Table1.fld2
FROM Table1 Table1
LEFT JOIN Table2 Table2
ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
WHERE Table2.fld1 IS NULL
If the pair exists in table2, then Table2.fld1 won't be NULL.|||> This should work:
> SELECT Table1.fld1, Table1.fld2
> FROM Table1 Table1
> LEFT JOIN Table2 Table2
> ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
> WHERE Table2.fld1 IS NULL
> If the pair exists in table2, then Table2.fld1 won't be NULL.
I was not clear in my labeling of cases. They are
Case 1: SELECTing on single fields present on Table 1 that should be
present on Table 2
Case 2: SELECTing on single fields present on Table 1 that should not be
present on Table 2
Case 3: SELECTing on multiple fields present on Table 1 that should be
present on Table 2
Case 4: SELECTing on multiple fields present on Table 1 that should not be
present on Table 2
As far as I know, JOINs can handle Cases 1-3 but not case 4
As far as I know, cascaded Selects can handle Cases 1-2 but not cases 3-4
Hrrrmmm after examination, you are right. Thanks
Stephen Howe|||Does this do what you want?:
select t1.fld1, t1.fld2
from Table1 t1
left outer join Table2 t2
on t1.fld1 = t2.fld1
and t1.fld2 = t2.fld2
where t2.fld1 is null;
HTH
Vern
"Stephen Howe" wrote:

> Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
> I have to write this is a INNER JOIN which will work. At least there is a
> solution here.
> But consider the 4th case. I am stuffed here regardless of doing Cascaded
> Select or Join. You want to say
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FR
OM
> Table2)
> And I cannot rewrite this as an inner join because rows are only considere
d
> when both fields, _jointly_ are absent from Table2.
> There must be a solution here for multiple fields.
> In real life I do have 2 tables. And I can gurantee that fld1 is present o
n
> both tables. But I am interested in listing the pairs of fields (fld1, fld
2)
> present on one table but absent on the other.
> Question1) Can it be done with Cascade Selects? How?
> Question2) Can it be done with Joins? How?
> Thanks
> Stephen Howe
>
>
>|||Stephen Howe (stephenPOINThoweATtns-globalPOINTcom) writes:
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
There is in ANSI SQL I believe, but ont implemented in SQL Server.
Anyway, all you need to learn is the EXISTS clause:
SELECT fld1, fld2
FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE a.fld1 = b.fld1
AND a.fld2 = b.fld2)
For the corresponding to NOT IN, use NOT EXISTS.
EXISTS/NOT EXISTS is in my opinion superior to IN/NOT IN. There is a
trap with nullable columns that you easily can fall into with NOT IN.
Does not happen with NOT EXISTS.
Performancewise, IN/NOT IN queries are usually rewritten internally
to EXISTS /NOT EXISTS, so there is no difference.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Performancewise, IN/NOT IN queries are usually rewritten internally
> to EXISTS /NOT EXISTS, so there is no difference.
Thanks Erland and everyone else.
My mind has been opened.
Stephen Howe