Table Comparisons - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table Comparisons

Author

Topic

avalentino
Starting Member

2 Posts

Posted - 2006-09-14 : 05:39:04

Hi SQLTeam,

a lil help here!

Let say, I've the following tables:

ShortlistTable
=============
shortlistID | EmployerID | ApplicantName
01 | 12 | James
02 | 15 | Peter
03 | 12 | Xander
04 | 12 | John
05 | 15 | Benjie

ApplicantTable
==============
Applicant Name | Specialization
James | Gardening
Peter | Pottery
Xander | Gardening
John | Gardening
Trevor | Gardening
Benjie | Pottery

Supposed I logged on as EmployerID# 12 and again search for Applicants to be shortlisted who has "Gardening" as their specialization, the record set should return only:

ApplicantName | Specialization
Trevor | Gardening

since I have already shorlisted other applicants who have the similar Specialization.

But if I logged on with a different EmployerID, let's say, EmployerID#15 and then search for the same specialization, "Gardening", the search result should be like this:

ApplicantName | Specialization
James | Gardening
Xander | Gardening
John | Gardening
Trevor | Gardening

since there's still no entry on the ShortListTable under EmployeeID#15 with the "Gardening" shorlisted applicants.

Is there a query for this dilemma that I have OR should I use a Stored Procedure for this matter. By the way, I'm using the .cfm (coldfusion language) as my web programming platform and MS SQL as my database server.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 05:55:17

[code]
-- Declare table variable for testing
declare @ShortlistTable table
(
shortlistID int,
EmployerID int,
ApplicantName varchar(100)
)

declare @ApplicantTable table
(
ApplicantName varchar(100),
Specialization varchar(100)
)

-- Insert some data for testing
insert into @ShortlistTable
select 01 , 12 , 'James' union all
select 02 , 15 , 'Peter' union all
select 03 , 12 , 'Xander' union all
select 04 , 12 , 'John' union all
select 05 , 15 , 'Benjie'

insert into @ApplicantTable
select 'James' , 'Gardening' union all
select 'Peter' , 'Pottery' union all
select 'Xander' , 'Gardening' union all
select 'John' , 'Gardening' union all
select 'Trevor' , 'Gardening' union all
select 'Benjie' , 'Pottery'

-- Variable
declare @EmployerID int,
@Specialization varchar(100)

select @EmployerID = 15,
@Specialization = 'Gardening'

-- Query
select *
from @ApplicantTable a
where Specialization = @Specialization
and not exists (select * from @ShortlistTable s where s.EmployerID = @EmployerID and a.ApplicantName = s.ApplicantName)
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 06:03:28

[code]
Declare @ShortlistTable Table
(
shortlistID varchar(10),
EmployerID varchar(10),
ApplicantName varchar(20)
)

Declare @ApplicantTable Table
(
ApplicantName varchar(100),
Specialization varchar(100)
)

Insert @ShortListTable
Select '01','12','James' Union All
Select '02','15','Peter' Union All
Select '03', '12','Xander' Union All
Select '04','12','John' Union All
Select '05','15','Benjie'

Insert @ApplicantTable
Select 'James','Gardening' Union All
Select 'Peter', 'Pottery' Union All
Select 'Xander','Gardening' Union All
Select 'John', 'Gardening' Union All
Select 'Trevor', 'Gardening' Union All
Select 'Benjie', 'Pottery'

When EmployeeId is 12
Declare @ID varchar(10) , @Spec varchar(100)
Set @ID = '12'
Set @Spec = 'Gardening'

Select * From @ApplicantTable a Where Not Exists
(Select ApplicantName From @ShortListTable b Where a.ApplicantName = b.ApplicantName And EmployerID = @ID )
And Specialization = @Spec

When EmployeeId is 15
Set @ID = '15'

Select * From @ApplicantTable a Where Not Exists
(Select ApplicantName From @ShortListTable b Where a.ApplicantName = b.ApplicantName And EmployerID = @ID )
And Specialization = @Spec

[/code]

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 06:04:24

I should have refreshed my browser :-(
[SNIPPED]

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 06:14:09

quote:
Originally posted by chiragkhabaria

I should have refreshed my browser :-(

Chirag

Yes. then you can just copy & paste the table declaration & sample data. Save some typing time

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 06:20:49

>>>Yes. then you can just copy & paste the table declaration & sample data. Save some typing time

but i really think, it will be better if the orginal poster post with the sample table, insert statements..

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 10:14:23

quote:
Originally posted by chiragkhabaria

>>>Yes. then you can just copy & paste the table declaration & sample data. Save some typing time

but i really think, it will be better if the orginal poster post with the sample table, insert statements..

Chirag

Agreed

avalentino
Starting Member

2 Posts

Posted - 2006-09-20 : 07:03:56

Hey khtan and chiragkhabaria,

Thanks for the replies! The Query that you guys both gave all work!

Muchas Gracias!

avalentino

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources