Complex join?

Is there another way to resolve the following problem
(instead of using a cursor for Tab1, especially if the table is "big"):
Suppose you have the following table:
Tab1:
col1
a
b
Tab2:
Col1 Col2
a c
b d
Or
Tab2:
Col1 Col2
a b
c d
Or
Tab2:
Col1 Col2
a c
d b
We want to check if all values in Col1 (a, b) from Tab1, occur somewhere as
a value in Col1 or Col2 from Tab2 but not necessary on the same row.
If both values can be found (see examples above) 1 should be returned.
So suppose Tab2 would be:
Tab2:
Col1 Col2
a c
d e
In this case 0 will be returned.
Thanx in advance for any help on this...How about...
select col1 from Tab1
left join Tab2
on
Tab1.Col1 = Tab2.Col1 or Tab1.Col1 = Tab2.Col2
where not Tab2.Col1 is null
Bryce|||Try,
if (
select
count(distinct a.col1)
from
t1 as a
inner join
t2 as b
on a.col1 = b.col1 or a.col1 = b.col2
) = (select count(*) from t1) and (select count(*) from t1) > 0
print 1
else
print 0
go
AMB
"PeterM" wrote:

> Is there another way to resolve the following problem
> (instead of using a cursor for Tab1, especially if the table is "big"):
> Suppose you have the following table:
> Tab1:
> col1
> a
> b
> Tab2:
> Col1 Col2
> a c
> b d
> Or
> Tab2:
> Col1 Col2
> a b
> c d
> Or
> Tab2:
> Col1 Col2
> a c
> d b
> We want to check if all values in Col1 (a, b) from Tab1, occur somewhere
as
> a value in Col1 or Col2 from Tab2 but not necessary on the same row.
> If both values can be found (see examples above) 1 should be returned.
> So suppose Tab2 would be:
> Tab2:
> Col1 Col2
> a c
> d e
> In this case 0 will be returned.
> Thanx in advance for any help on this...|||The following might work to return 1 or 0. However, it is highly
dependent on the first table having only 2 records. If what you gave
was a minimalist example of what you are trying to do, you may want to
be more precise.
SELECT Tab1.Col1
FROM Tab1
INNER JOIN Tab2
ON Tab1.Col1 = Tab2.Col1 OR Tab1.Col1 = Tab2.Col2
SELECT @.@.ROWCOUNT -1|||On Fri, 11 Mar 2005 07:41:08 -0800, PeterM wrote:

>We want to check if all values in Col1 (a, b) from Tab1, occur somewhere a
s
>a value in Col1 or Col2 from Tab2 but not necessary on the same row.
>If both values can be found (see examples above) 1 should be returned.
(snip)
>In this case 0 will be returned.
Hi Peter,
If you only need 0 or 1 for existence or not-existence, use
IF EXISTS (SELECT Tab1.Col1
FROM Tab1
INNER JOIN Tab2
ON Tab1.Col1 = Tab2.Col1
OR Tab1.Col1 = Tab2.Col2)
RETURN 1
ELSE
RETURN 0
The above assumes you need the 1 or 0 returned as return value from a
stored procedure. If you need to store it in a variable, use
SELECT CASE
WHEN EXISTS (SELECT Tab1.Col1
FROM Tab1
INNER JOIN Tab2
ON Tab1.Col1 = Tab2.Col1
OR Tab1.Col1 = Tab2.Col2)
THEN 1
ELSE 0
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Great works perfect!! Thanx a lot
Peter
"Alejandro Mesa" wrote:
> Try,
> if (
> select
> count(distinct a.col1)
> from
> t1 as a
> inner join
> t2 as b
> on a.col1 = b.col1 or a.col1 = b.col2
> ) = (select count(*) from t1) and (select count(*) from t1) > 0
> print 1
> else
> print 0
> go
>
> AMB
> "PeterM" wrote:
>|||Thank You very much works great!!!!
Peter
"bd" wrote:

> How about...
> select col1 from Tab1
> left join Tab2
> on
> Tab1.Col1 = Tab2.Col1 or Tab1.Col1 = Tab2.Col2
> where not Tab2.Col1 is null
> Bryce
>|||Hugo,
I tried the code but it returned 1 even if one of the two values was not
present in the second table:
suppose:
Tab1:
byd_nat
mlt
Tab2:
sig abu_data
ltw onss
sig byd_nat
onscc sig
the answer should be 0 because mlt was not found.
Peter
"Hugo Kornelis" wrote:

> On Fri, 11 Mar 2005 07:41:08 -0800, PeterM wrote:
>
> (snip)
> Hi Peter,
> If you only need 0 or 1 for existence or not-existence, use
> IF EXISTS (SELECT Tab1.Col1
> FROM Tab1
> INNER JOIN Tab2
> ON Tab1.Col1 = Tab2.Col1
> OR Tab1.Col1 = Tab2.Col2)
> RETURN 1
> ELSE
> RETURN 0
> The above assumes you need the 1 or 0 returned as return value from a
> stored procedure. If you need to store it in a variable, use
> SELECT CASE
> WHEN EXISTS (SELECT Tab1.Col1
> FROM Tab1
> INNER JOIN Tab2
> ON Tab1.Col1 = Tab2.Col1
> OR Tab1.Col1 = Tab2.Col2)
> THEN 1
> ELSE 0
> END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||If I understand your request, you want to determine if every value in Table
1
exists in Table 2 (and you don't care which column it's in)?
If so, then this will work
Case When Not Exists
(Select * From Table1 T1
Where Not Exsts
(Select * From Table2
Where T1.Col1 In Col1, Col2)))
Then 1 Else 0 End
... Effectively, If there are no records in Table1 Where the the Col1 Value
does not exists in Col1 or Col2 in any record of Table2 (i.e., anywhere in
Table2) Then
Every value in Col1 of Table1 is SOMEWHERE in Table2, so
return 1, Else return 0
"PeterM" wrote:

> Is there another way to resolve the following problem
> (instead of using a cursor for Tab1, especially if the table is "big"):
> Suppose you have the following table:
> Tab1:
> col1
> a
> b
> Tab2:
> Col1 Col2
> a c
> b d
> Or
> Tab2:
> Col1 Col2
> a b
> c d
> Or
> Tab2:
> Col1 Col2
> a c
> d b
> We want to check if all values in Col1 (a, b) from Tab1, occur somewhere
as
> a value in Col1 or Col2 from Tab2 but not necessary on the same row.
> If both values can be found (see examples above) 1 should be returned.
> So suppose Tab2 would be:
> Tab2:
> Col1 Col2
> a c
> d e
> In this case 0 will be returned.
> Thanx in advance for any help on this...|||Hi Marfig,
Indeed the example was only to show the problem but should work in any
number of rows in both tables.
Peter
"Marfig" wrote:

> The following might work to return 1 or 0. However, it is highly
> dependent on the first table having only 2 records. If what you gave
> was a minimalist example of what you are trying to do, you may want to
> be more precise.
> SELECT Tab1.Col1
> FROM Tab1
> INNER JOIN Tab2
> ON Tab1.Col1 = Tab2.Col1 OR Tab1.Col1 = Tab2.Col2
> SELECT @.@.ROWCOUNT -1
>