asked on

find if a record field from table a exists in table b ms sql 2005

I have a table a with field called name:

john
henry
mike
bob
john
frank

I have anothes similar table b. sme fields.

I need a quesry tal will return the records of table a togethoer with a result if the the name of each record has a match in table b. Maybe table b has multiple matches for john i do not care I just need a result that will say that john has a match or that it does not. and the same for every record.

ASKER CERTIFIED SOLUTION

jimyX

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robrodp

ASKER

Thx. Great solutions

robrodp

ASKER

In the query:

Select T1.FieldName, (case when T1.FieldName in (select T2.FieldName from Table2 T2 where T2.FieldName = T1.FieldName) then 'Exist' else 'Not exist' end) as Matches from Table1 T1

How can I include in the result table a field from T2?

Bhavesh Shah

Hi,

check out this query.

you can include the result of T2 also

- Bhavesh

SELECT T1.FieldName, Case When T2.FieldName IS NOT NULL THEN 'Match' ELSE 'Not Match' END AS MatchRow
FROM
	Table1 T1 LEFT OUTER JOIN Table2 T2
ON T1.FieldName = T2.FieldName

Open in new window

jimyX

Select T2.Fieldx, T1.FieldName, (case when T1.FieldName in (select T2.FieldName from Table2 T2 where T2.FieldName = T1.FieldName) then 'Exist' else 'Not exist' end) as Matches from Table1 T1 join Table2 T2 on T2.Fieldx = T1.Fieldy

T2.Fieldx is relative to T1.Fieldy (the relational columns).