joining 3 tables not getting valid resultset

Question

Post reply

joining 3 tables not getting valid resultset

User7766

SSCommitted

Points: 1969
More actions
September 6, 2011 at 3:27 am

#390346

hi all,
I need to join 4 tables 3 are in one database and the other table is in other database,
Tables : Jobreq, CandidateInterview, Candidates, Status. I need to show candidates count for ALL jobs based on status, I tried getting the resultset using below query, But it is not showing the proper COUNT of candidates for a particular Status.
Can anyone please help me out to get resultset based on 3 tables
USE [RecruitmentDB]
GO
/****** Object: StoredProcedure [dbo].[usp_get_all_jobreqselectedcandidates] Script Date: 09/06/2011 14:44:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_get_all_jobreqselectedcandidates]
AS
BEGIN
SELECT j.JobReqID,j.JobReqCode,j.DateAdded,count(*) AS SelectedCandidates
FROM ResumesDB.dbo.Candidates c
FULL OUTER JOIN RecruitmentDB.dbo.CandidateInterview ci on ci.CandidateID=c.CandidateID
FULL OUTER JOIN RecruitmentDB.dbo.JobRequisition j on j.JobReqID=ci.JobReqID
LEFT OUTER JOIN RecruitmentDB.dbo.Status s on s.StatusID=ci.StatusID
AND ci.StatusID='069DDDEC-6127-4DB7-AEB3-DA76B2266DD4'
GROUP BY j.JobReqID,j.JobReqCode,j.DateAdded
ORDER BY j.DateAdded Desc
END
I want all records from table 'j' and total no:of applied candidates from table c based on statusid('069DDDEC-6127-4DB7-AEB3-DA76B2266DD4') in table ci.
Thanks,

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 1

ghanshyam.kundu

Hall of Fame

Points: 3337

September 6, 2011 at 3:39 am

#1377251

could u post ur DDL and some sample data.

User7766 SSCommitted Points: 1969 More actions · Answer 2

Hi,

Please find the below screen which displays the resultset for the above join.. you can see the column selectedcandidates there with count which is not correct.

User7766 SSCommitted Points: 1969 More actions · Answer 3

Please find the sample data screen attached herewith from CandidateInterview table, where we can see the no:of candidates applied for a job for particular status..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

maruthipuligandla (9/6/2011)
Please find the sample data screen attached herewith from CandidateInterview table, where we can see the no:of candidates applied for a job for particular status..

Please supply DDL and DML as requested. This is a trivial issue made impossible by the absence of objects to test against. If you are unsure of how to proceed with this, then read the link below.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

User7766 SSCommitted Points: 1969 More actions · Answer 5

Hi,

I didnt get what exactly you asking me for? Should i send scripts for 3 tables with data or what?

I'm someway nearer to the solution, When i changed my query as below, this time i can see exact candidate's count, But i cannot see all the records from Jobrequisition table..

SELECT j.JobReqID,j.JobReqCode,j.DateAdded,count(ci.JobReqID) AS SelectedCandidates

FROM RecruitmentDB.dbo.CandidateInterview ci

FULL OUTER JOIN ResumesDB.dbo.Candidates c on ci.CandidateID=c.CandidateID

FULL OUTER JOIN RecruitmentDB.dbo.JobRequisition j on j.JobReqID=ci.JobreqID

LEFT OUTER JOIN RecruitmentDB.dbo.Status s on s.StatusID=ci.StatusID

where ci.StatusID='069DDDEC-6127-4DB7-AEB3-DA76B2266DD4'

GROUP BY j.JobReqID,j.JobReqCode,j.DateAdded

ORDER BY j.DateAdded Desc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

maruthipuligandla (9/6/2011)
Hi,
I didnt get what exactly you asking me for? Should i send scripts for 3 tables with data or what?...

Four tables. If you take the time to read the article, it will show you exactly how to prepare and post scripts to create tables and populate them with a useful amount of data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

User7766 SSCommitted Points: 1969 More actions · Answer 7

Hi,

I've generated scripts with data for 4 tables, please find the attached sql script files..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

maruthipuligandla (9/6/2011)
Hi,
I've generated scripts with data for 4 tables, please find the attached sql script files..

The image columns aren't required for this exercise, take up an inordinate amount of space, and cause SSMS to grind almost to a halt - can you please post the INSERTS substituting NULL for these columns? Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

User7766 SSCommitted Points: 1969 More actions · Answer 9

I did it in a hurry, sorry. please find the attached insert scripts..

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 10

what data you have provided is not sufficient, take some time to follow the link given above to post a question in the forum, and post the proper information.

although i took a chance to modify the query given by you try it if it is working for you.

SELECT j.JobReqID,j.JobReqCode,j.DateAdded,count(candidateID) AS SelectedCandidates

FROM ResumesDB.dbo.Candidates c

INNER JOIN RecruitmentDB.dbo.CandidateInterview ci on ci.CandidateID=c.CandidateID

INNER JOIN RecruitmentDB.dbo.JobRequisition j on j.JobReqID=ci.JobReqID

INNER JOIN RecruitmentDB.dbo.Status s on s.StatusID=ci.StatusID

AND ci.StatusID='069DDDEC-6127-4DB7-AEB3-DA76B2266DD4'

GROUP BY j.JobReqID,j.JobReqCode,j.DateAdded,ci.StatusID

ORDER BY j.DateAdded Desc

User7766 SSCommitted Points: 1969 More actions · Answer 11

ChrisM@Work (9/6/2011)
maruthipuligandla (9/6/2011)
Hi,
I've generated scripts with data for 4 tables, please find the attached sql script files..
The image columns aren't required for this exercise, take up an inordinate amount of space, and cause SSMS to grind almost to a halt - can you please post the INSERTS substituting NULL for these columns? Thanks.

Hi,

Any update on the query? I've rewritten it, but i can only see 2 rows and the unmatched rows are not displaying..

SELECT j.JobReqID,j.JobReqCode,j.DateAdded,count(c.CandidateID) AS SelectedCandidates

FROM RecruitmentDB.dbo.CandidateInterview ci

LEFT OUTER JOIN ResumesDB.dbo.Candidates c on ci.CandidateID=c.CandidateID

LEFT OUTER JOIN RecruitmentDB.dbo.JobRequisition j on j.JobReqID=c.JobreqID

WHERE ci.StatusID='069DDDEC-6127-4DB7-AEB3-DA76B2266DD4'

GROUP BY j.JobReqID,j.JobReqCode,j.DateAdded

ORDER BY j.DateAdded Desc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

maruthipuligandla (9/6/2011)
...Any update on the query? ...

Nope, still waiting for usable sample data scripts.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 13

It seems to me you're only looking at data in two tables. Try this on for size:

SELECT a.JobReqID, a.JobReqCode, a.DateAdded, COUNT(DISTINCT c.CandidateID) AS SelectedCandidates

FROM RecruitmentDB.dbo.JobRequisition a

LEFT JOIN RecruitmentDB.dbo.CandidateInterview b

ON b.JobreqID = a.JobReqID

AND b.StatusID = '069DDDEC-6127-4DB7-AEB3-DA76B2266DD4'

GROUP BY a.JobReqID, a.JobReqCode, a.DateAdded

ORDER BY a.DateAdded DESC

If you want to see every requisition, but only count candidates with a particular status, then you need the predicate for the status in the join, not the where clause, because you want the join to fail if the candidate's status is wrong, not to exclude the row for the requisition. Otherwise you'll end up dropping reqs from the result set where every candidate has the wrong status.