distinct value in left join(urgent)

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)

distinct value in left join(urgent)

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-29 : 03:21:57

I have two table: Job and JobapplyRecord
When i use the left join statement, i get

JobID JobApplyRecordID
7.............null
8....... 6
8....... 7
8....... 8
9..............10

However, my expected result: I only need to know whether recordID is null or not
For example
JobID JobApplyRecordID
7.............null
8....... "exist"
9..............10

How to do this?

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 03:36:02

Do you want to get only one record for each job id?
What do you mean by exists for 8?
Post the query you used?

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-29 : 03:44:11

sorry , i forget to post the query

select JobID, JObApplyRecordID
from job left join jobapplyrecord

There may/may not have apply record for each job.

My purpose is to know whether a job have apply record(no matter the no), if yes, then show exist or sth in the cell.

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 03:48:55

Try this

select JobID, case when JObApplyRecordID is not null then 'Exists' end
from job left join jobapplyrecord

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-29 : 03:56:51

My desired output is that the jobid is distinct
EG
JobID JobApplyRecordID
7.............null
8....... "exist"
9..............10

But your query result into
JobID JobApplyRecordID
7.............null
8....... "exist"
8....... "exist"
8....... "exist"
9..............10

I find i cannot use the "distinct". How to solve this ?

be a hardworking people!!

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-08-29 : 03:59:17

[code]
select JobID,
case (select count(JObApplyRecordID)
from jobApplyRecord
where jobApplyRecord.jobid = job.jobid)
when 0 then null
when 1 then cast(jobapplyrecordid as varchar(11))
else 'Exist' end
from job
[/code]

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-29 : 04:16:54

Thanks!
Only distinct(Jobid) is need at this case!

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 04:17:29

Then use this query

select Distinct JobID, case when JObApplyRecordID is not null then 'Exists' end
from job left join jobapplyrecord

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources