SQL 2000 Intersect Equivalent

Posted on 2006-10-30
Last Modified: 2008-01-09
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.
Question by:porkVT
  • 2

Expert Comment

ID: 17835555
Select * from Table1 t1 where t1.CaseNumber in (Select CaseNumber from Table2)
Select * From Table2 t2 where t1.CaseNumber in (Select CaseNumber from Table1)

Accepted Solution

srafi78 earned 125 total points
ID: 17835561
Syntax error...

Select * from Table1 t1 where t1.CaseNumber in (Select CaseNumber from Table2)
Select * From Table2 t2 where t2.CaseNumber in (Select CaseNumber from Table1)
LVL 11

Expert Comment

ID: 17835699
Hi srafi78,

try this

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


Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Complex SQL 10 34
Copy Database Wizard Error 3 22
Sql query for filter 12 23
Caste datetime 2 24
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now