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.
| Author |
Topic |
|
Hobbsy2011
Starting Member
5 Posts |
 Posted - 2011-07-06 : 10:28:36
|
| Hello,I'm working on a sproc that needs some additional columns adding to it from a different table, this would normally be quite stright forward BUT it's hierachical data in a one to many relationship.I don't want to retrieve all the hierachical data, all I need is a couple of additional columns from the last "linked" record that was added to the table..I've had a go at putting a SELECT query within my query, but becuase I need 3 bits of data & not just one I get a "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" message..I'm not sure of the best way to do this, but at the minute i'm trying to fudge the 2 statements below together as one & i'm not getting very far!! lol Do you guys have any suggestions as to the correct/best way to acheive this?The query below retrieves the additional record I need..SELECT TOP (1) SentDate, MessageType, LU_EmailMessages.MessageDescriptionFROM Personnel_ClientDirect_SentEmailLog LEFT OUTER JOIN LU_EmailMessages ON Personnel_ClientDirect_SentEmailLog.MessageID = LU_EmailMessages.MessageIDWHERE (JobTitleID = @JobTitleID) AND (CandidateID = @CandidateID)ORDER BY RecordID DESC& I need to add it somehow to the partial query below... WITH Candidates as ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY RecordDate) as RowNumber, Personnel_ProjectLink.PersonID, Personnel_ProjectLink.JobTitleID, Personnel_ProjectLink.JobBoardID, Personnel_ProjectLink.CV_ID, Personnel_Master.Town, Personnel_Master.PhoneNumber, Personnel_ProjectLink.RecordDate, Personnel_ClientDirectStatus.ClientStatus, Personnel_ClientDirectStatus.CurrentTransaction, Personnel_ClientDirectStatus.Reason, Personnel_CVs_Master.CV_DocType, Personnel_Master.EmailAddress, Personnel_ClientDirectStatus.StatusDate, Personnel_ClientDirectStatus.TransactionDateTime, dbo.fn_getCandidateRating (Personnel_ProjectLink.TotalScoreRating) as 'Rating'FROM [Vacancies _Master] INNER JOIN Personnel_ProjectLink ON [Vacancies _Master].JobTitleID = Personnel_ProjectLink.JobTitleID LEFT OUTER JOIN Personnel_ClientDirectStatus ON Personnel_ProjectLink.PersonID = Personnel_ClientDirectStatus.PersonID AND Personnel_ProjectLink.JobTitleID = Personnel_ClientDirectStatus.JobTitleID LEFT OUTER JOIN Personnel_Master ON Personnel_ProjectLink.PersonID = Personnel_Master.PersonID LEFT OUTER JOIN Personnel_CVs_Master ON Personnel_ProjectLink.PersonID = Personnel_CVs_Master.PersonID AND Personnel_ProjectLink.CV_ID = Personnel_CVs_Master.CV_IDWHERE ([Vacancies _Master].JobTitleID = @jobTitleID) AND ([Vacancies _Master].FlexUserID = @flexUserID) AND (Personnel_ClientDirectStatus.ClientStatus = @Status))Any Ideas/suggestions welcome.. Thankyou for your time. |
|
|
Hobbsy2011
Starting Member
5 Posts |
Posted - 2011-07-06 : 10:59:57
|
| Hi Guys,It's ok, please don't waste your time with this problem now, I have changed the business logic slightly so it only brings back 1 additional column & still gives me the desired result.BEGIN SELECT @rowCount = (SELECT COUNT(*) FROM Personnel_ProjectLink INNER JOIN Personnel_ClientDirectStatus ON Personnel_ProjectLink.PersonID = Personnel_ClientDirectStatus.PersonID AND Personnel_ProjectLink.JobTitleID = Personnel_ClientDirectStatus.JobTitleID WHERE (Personnel_ProjectLink.JobTitleID = @jobTitleID) AND (Personnel_ClientDirectStatus.ClientStatus = @Status)) WITH Candidates as ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY RecordDate) as RowNumber, Personnel_ProjectLink.PersonID, Personnel_ProjectLink.JobTitleID, Personnel_ProjectLink.JobBoardID, Personnel_ProjectLink.CV_ID, Personnel_Master.Salutation + ' ' + Personnel_Master.FirstName + ' ' + Personnel_Master.LastName AS 'sName', Personnel_Master.Town, Personnel_Master.PhoneNumber, Personnel_ProjectLink.RecordDate, Personnel_ClientDirectStatus.ClientStatus, Personnel_ClientDirectStatus.CurrentTransaction, Personnel_ClientDirectStatus.Reason, Personnel_CVs_Master.CV_DocType, Personnel_Master.EmailAddress, Personnel_ClientDirectStatus.StatusDate, Personnel_ClientDirectStatus.TransactionDateTime, dbo.fn_getCandidateRating(Personnel_ProjectLink.TotalScoreRating) as 'Rating', (SELECT TOP (1) SentDate FROM Personnel_ClientDirect_SentEmailLog WHERE (JobTitleID = @jobTitleID) AND (CandidateID = Personnel_ProjectLink.PersonID) ORDER BY RecordID DESC) as 'EmailContact' FROM [Vacancies _Master] INNER JOIN Personnel_ProjectLink ON [Vacancies _Master].JobTitleID = Personnel_ProjectLink.JobTitleID LEFT OUTER JOIN Personnel_ClientDirectStatus ON Personnel_ProjectLink.PersonID = Personnel_ClientDirectStatus.PersonID AND Personnel_ProjectLink.JobTitleID = Personnel_ClientDirectStatus.JobTitleID LEFT OUTER JOIN Personnel_Master ON Personnel_ProjectLink.PersonID = Personnel_Master.PersonID LEFT OUTER JOIN Personnel_CVs_Master ON Personnel_ProjectLink.PersonID = Personnel_CVs_Master.PersonID AND Personnel_ProjectLink.CV_ID = Personnel_CVs_Master.CV_ID WHERE ([Vacancies _Master].JobTitleID = @jobTitleID) AND ([Vacancies _Master].FlexUserID = @flexUserID) AND (Personnel_ClientDirectStatus.ClientStatus = @Status) ) Select PersonID, JobTitleID, JobBoardID, CV_ID, sName, Town, PhoneNumber, RecordDate, ClientStatus, CurrentTransaction, Reason as 'Result', CV_DocType, EmailAddress, TransactionDateTime, Rating, EmailContact FROM Candidates WHERE RowNumber Between @StartRowIndex AND @StartRowIndex+@NumRows-1 ORDER BY Rating desc, StatusDate descEND |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-06 : 11:06:26
|
It is really not easy to solve this problem without knowing the table structure and so on...but I see one way:I'm guessing you are able to add your needs to the original query BUT then there will be more rows than you need because of the to many relationship and only the not needed additional rows in your resultset are a problem for you.So please show us the query including the additional joins and we can show you how to eliminate the "duplicate" rows then...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-06 : 11:08:07
|
OK if that fits your needs then everything is fine 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Hobbsy2011
Starting Member
5 Posts |
Posted - 2011-07-07 : 04:56:56
|
quote:
Originally posted by webfred
It is really not easy to solve this problem without knowing the table structure and so on...but I see one way:I'm guessing you are able to add your needs to the original query BUT then there will be more rows than you need because of the to many relationship and only the not needed additional rows in your resultset are a problem for you.So please show us the query including the additional joins and we can show you how to eliminate the "duplicate" rows then...
No, you're never too old to Yak'n'Roll if you're too young to die.
Hi WebFred,Your exactly right, In my original query I was getting "duplicate" rows & your solution would have been an ideal method to solve the problem. It's ok now though all works well with the aditional select query. Thank you for taking the time to look at my problem, it's very much appreciated. |
|
|
|
|
|
|
|
|
Whats the best way to do this?