[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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