Solved

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

Posted on 2011-03-13
6
228 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
ID: 35125052
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
ID: 35125073
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
ID: 35125095
Thx. Great solutions
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:robrodp
ID: 35125242
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
ID: 35125626
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
ID: 35130919
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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