matching tables ... any idea? - SOLVED - Thanks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

matching tables ... any idea? - SOLVED - Thanks

Author

Topic

Cypher
Starting Member

3 Posts

Posted - 2010-09-01 : 06:12:09

Hello guys.. Im kinda new here and i dont have a native tongue so pls bear with my english..

ok here goes..

create table t1
(
disease varchar(5) not null,
symptom varchar(5) not null
)
create table t2
(
symptom varchar(5) not null,
)

insert into t1
select 'A','X' union
select 'A','Y' union
select 'B','X' union
select 'B','Y' union
select 'C','X' union
select 'C','Y' union
select 'D','X'

insert into t2
select 'X' union
select 'Y'

The goal is to get the "disease" from t1 where t1.symptom is perfectly equal to t2.symptom .. and i mean equal on all rows..
so disease D is not supposed to be on the result since it only have 1 of the 2 symptoms requirement..

i only know the basics so forgive me for being stupid..

i already tried searchin on the net.. no luck on that.. actually i've been looking for some answer since yesterday.. im starting to lose temper lol

i really hope someone here can help me..

thanks a lot in advance

-Cypher

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2010-09-01 : 06:14:33

Can you send an example of the data in both tables

Whitmoj
If I have inspired one person today then my job is done.

Cypher
Starting Member

3 Posts

Posted - 2010-09-01 : 06:26:51

hmmm data? i think i already posted it on my first post..
anyway..

so table t1 is supposed to look something like this..

|disease|symptom|
| A | X |
| A | Y |
| B | X |
| B | Y |
| C | X |
| C | Y |
| D | X |

table t2 looks something like this..

|symptom|
| X |
| Y |

There.. not sure if that's exactly what you're asking though..

ow.. and thanks for your reply

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-01 : 06:36:28

I don't have sql server installed to test- Hopefully this works

select disease 
from
(
select disease,(case when exists(select 1 from t2 where t1.symptom=t2.symptom) then 1 else 0 end) as cnt from t1  
)x 
group by disease
having sum(cnt)=(select count(distinct symptom) from t2)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-01 : 06:37:59

Also, I was thinking-- Don't you have a column for disease in the 2nd table so it lists all symtoms for a disease? If you do, You might need to add a join condition to above.

Cypher
Starting Member

3 Posts

Posted - 2010-09-01 : 06:51:07

wow that works great! though I dont have a clue on what those codes means. hehe
thanks a lot!

by the way.. no, i dont have a disease column on my second table.. but i do have another column (identity) so i can delete the latest entry.. hope that wont mess the codes ..

thanks again!

urzsuresh
Starting Member

30 Posts

Posted - 2010-09-01 : 07:43:33

HI KINDLY TRY THE BELOW ONE. YOU WILL GET IDEA.LET ME KNOW THAT, DO YOU NEED ANY FURTHER CLARIFFICATION

 
Declare @t1 table 
(
disease varchar(5) not null,
symptom varchar(5) not null
)
Declare  @t2 table 
(
symptom varchar(5) not null
)
insert into @t1
select 'A','X' union
select 'A','Y' union
select 'B','X' union
select 'B','Y' union
select 'C','X' union
select 'C','Y' union
select 'D','X'

insert into @t2
select 'X' union
select 'Y'



;with cte
as
(
Select a.Disease,b.Symptom,row_number() over(partition by a.disease order by a.disease) rn from @t1 a 
Join @t2 b
on a.Symptom=b.Symptom
)select Disease from cte  where rn=2

Suri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources