Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

SQL 2000 Intersect Equivalent

I am looking for a way to get an intersection of 2 tables in a SQL Server 2000 database.  I know 2005 offers this functionality built in, but 2000 lacks this.  I'm guessing there is still a way to accomplish this with what 2000 does offer, probably with some tricky subqueries, but I haven't been able to figure it out yet.

Basically I have 2 tables that have a CaseNumber field and I want to get a recordset that lists the CaseNumbers that appear in both table1 and table2.  Anyone have some SQL code lying around that will accomplish this?  Thanks in advance.
0
porkVT
Asked:
porkVT
  • 2
1 Solution
 
srafi78Commented:
Select * from Table1 t1 where t1.CaseNumber in (Select CaseNumber from Table2)
UNION ALL
Select * From Table2 t2 where t1.CaseNumber in (Select CaseNumber from Table1)
0
 
srafi78Commented:
Syntax error...

Select * from Table1 t1 where t1.CaseNumber in (Select CaseNumber from Table2)
UNION ALL
Select * From Table2 t2 where t2.CaseNumber in (Select CaseNumber from Table1)
0
 
regbesCommented:
Hi srafi78,

try this

select distinct table1.casenumber
from table1 inner join table 2 on table1.casenumber = table2.casenumber

0

Featured Post

Technology Partners: 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!

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