Need function to match elements in one table to another
-
OK, so that wasn't very descriptive
, but you don't get a lot of room in the title, do you?I have two tables, for purposes of simplicity they look like this:
- Table A
- 1 A
- 2 C
- Table B
- 1 A
- 2 B
- 3 C
- 4 D
I need to come up with a function that returns True/False depending on whether or not all the entries in Table A are in Table B. The example above would yield a True return. If Table A were:
- 1 F
- 2 G
then the function would return False.
Anyone got a quick solution? I can't seem to get my mind wrapped around this one.
TIA
-
If Exists (Select * From TableA
Where SomeColumn Not In
(
Select SameColumn
From TableB
)
)
Return False
Else
Return True
End
-
how about an sp?
CREATE PROCEDURE [dbo].[truefalse]
@table1 varchar(50), @table2 varchar(50),
@answer varchar(1)='' OUTPUT
AS
--False
if (select sub.answer from (
select distinct case when t2.id is null then 'False' else 'True' end as answer
from table1 t1 left outer join table2 t2 on
t1.ID=t2.id and t1.[desc]=t2.[desc]
where t2.id is null and t2.[desc] is null)sub where sub.answer='false')='false' begin set @answer='F'
end
--True
else set @answer='T'
select @answer
GO
exec truefalse 'table','table2' -->returns a T or F
-
You could also do a left outer join, and see whether any records are returned. If none are returned, it means that all the records from table A are in table B
Regards
Schalk
-
You could left join for sure.
But what if the datasets are large ? Why pull a large result set to count it, when all you need to do is find the first occurrence where the condition isn't met ? Think of the IO cost ...
-
The lists are always small (1-4 entries against 1-6 entries). I used a variation of your code Rookie to implement it as a user defined function (had to change the Return logic so the last statement was a standalone Return).
- Declare @rtn bit
- If Exists (Select * From TableA
Where SomeColumn Not In
(
Select SameColumn
From TableB
)
)
Set @rtn=0
Else
Set @rtn=1
- Return(@rtn)
Works great. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply