Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to select rows from other table to compare

Posted on 2011-10-17
4
Medium Priority
?
273 Views
Last Modified: 2012-05-12
hi, i have 2 tables:

table1                                                         table2
id       number1      number2                           id               number1

1            10               7                                     1                    8
2             5                 3                                     2                   10
3              8                 3


i want create an select that makes the test to see if the table2 has the record number1 for table1. so we will create an table like this:

table3                                                        
id       number1      number2     haveregistry                        

1            10               7               true  
2             5                 3              false                      
3              8                 3              true


any idea?

0
Comment
Question by:rafaelrgl
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 668 total points
ID: 36983706
where did haveregistry come from?

Anyway, I would try something like this:

SELECT INTO table3 as SELECT tabl1.id, table1.number2, table1.number2, table2.id, table2.number2 from tabe1 left join table2 on table1.id = table2.id

Open in new window

0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 664 total points
ID: 36983720
insert into table3 (Id, number1, number2, haveregistry)
select Id, number1, number2,
      (case when exists (select 1 from table2 as x where x.number1 = number1) then 'true' else 'false' end) as haveregistry
from table1
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36983752
try
SELECT tabl1.id, table1.number1, table1.number2, 
	case 
		when table2.number1 IS null then 
			'false' 
		else 
			'true' 
	end [haveregistry]
from tabe1 
left join table2 on table1.id = table2.id

Open in new window

0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 668 total points
ID: 36983757
Thats not correct, you are checking for number1 field, so use that to join
SELECT tabl1.id, table1.number1, table1.number2, 
	case 
		when table2.number1 IS null then 
			'false' 
		else 
			'true' 
	end [haveregistry]
from tabe1 
left join table2 on table1.number1 = table2.number1

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 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