• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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.



0
robrodp
Asked:
robrodp
2 Solutions
 
jimyXCommented:
Try this please:
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
0
 
wdosanjosCommented:
It should be something like this:

select a.*, 
       CASE WHEN (select COUNT(1) from TableB b where b.name = a.name) > 0 
            THEN 'Yes' 
            ELSE 'No' 
            END Matched
  from TableA a

Open in new window

0
 
robrodpAuthor Commented:
Thx. Great solutions
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
robrodpAuthor Commented:
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?
0
 
Bhavesh ShahLead AnalysistCommented:
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

0
 
jimyXCommented:
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).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now