Solved

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

Posted on 2011-03-13
6
226 Views
Last Modified: 2012-05-11
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
Comment
Question by:robrodp
6 Comments
 
LVL 24

Accepted Solution

by:
jimyX earned 250 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:robrodp
Comment Utility
Thx. Great solutions
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:robrodp
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now