Solved

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

Posted on 2011-03-13
6
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 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