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

how to select rows from other table to compare

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
rafaelrgl
Asked:
rafaelrgl
  • 2
3 Solutions
 
sammySeltzerCommented:
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
 
sachitjainCommented:
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
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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