troubleshooting Question

Complicated Select & Insert statement - MS SQL 2008

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
5 Comments2 Solutions313 ViewsLast Modified:
I've got a really complicated SP to build (or at least it is to me) and I'm realy confused so forgive me if I make little sense -

Basically I'm being sent a number of variables, which include -

       @LocationID int,
      @JobTitle nvarchar(50),
      @category nvarchar(50),
      @username nvarchar(255),
                     @description nvarchar(max)

I have 4 main tables that are affected here these being
JBLocation - this has three columns JBLID (PK) JBLSiteID (SK) JBLocation - Nvarchar

JBAdvert - JBAID (PK) JBASiteID (SK), JBALocation Nvarchar + More

JBEmailNotification - JBENotificationID (PK), JBENSiteID (SK) JBENLocation nvarchar JBENCategory nvarchar JBENKeyword nvarchar

JBEmployee  - JBEID (PK), JBESiteID(SK) JBEUsername nvarchar



JBESent JBESJobID (SK), JBESSiteID (SK), JBESLocation, JBESCategory, JBESReceipients.

What I am trying to do is capture the JBAdvert (JBAID, JBASiteID, JBALocation, JBACategory) for the last record entered by @username where the (JBAdvert JBALocation = (JBLocation JBLocation) & JBLocation (JBLID = @LocationID.
And the number of JBES Recepients that match - this query produces a list of all of the recipients -

Select
Distinct(C.JBACAID),
C.JBACAUsername,
S.JBSURLShort,
S.JBSURL,
S.JBSNoReplyEmail
from dbo.JBEmailNotification EN
inner join dbo.JBACandidate C on C.JBACAID = EN.JBENcandidateID
inner join dbo.JBSite S on S.JBSSiteID = EN.JBENsiteID
inner join dbo.JBLocation L on L.JBLSiteID = EN.JBENsiteID
Where JBENlocation IN(SELECT JBLocation FROM dbo.JBLocation WHERE JBLID = 435)
AND JBENcategory = 'Secretarial' AND 'PA, Secretary, Office Manager, Administrator' like '%' + JBENKeyword + '%' (((Iv'e used variables here)))
And this Query produces the advert data -

Select top (1)
AD.JBAID,
AD.JBASiteID,
AD.JBALocation,
AD.JBACategory,
E.JBEID
FROM dbo.JBAdvert AD inner join dbo.JBEmployee E on E.JBEID = AD.JBAEmployeeID
WHERE E.JBEUsername = @username
ORDER BY AD.JBAID DESC



Hope I am making sense - but I'm really confused so probably not..

Please let me have your thoughts

GTN
CREATE PROCEDURE [dbo].[JobboardBBinsert]
	@LocationID int,
	@JobTitle nvarchar(50),
	@category nvarchar(50),
	@username nvarchar(255)
AS

INSERT INTO dbo.JBESent (JBESJobID, JBESSiteID, JBESLocation, JBESCategory, JBESReceipients)
Select Top (1)
AD.JBAID,
AD.JBASiteID, 
AD.JBALocation, 
AD.JBACategory,
EN.Recipients
FROM dbo.JBAdvert AD 
inner join dbo.JBEmailNotification EN
(Select Count(Distinct(JBENotificationID)) AS EN.Recipients from dbo.JBEmailNotification EN 
inner join dbo.JBLocation L on L.JBLSiteID = EN.JBENsiteID
Where JBENlocation IN(SELECT JBLocation FROM dbo.JBLocation WHERE JBLID = 435) 
AND JBENcategory = 'Secretarial' AND 'PA, Secretary, Office Manager, Administrator' like '%' + JBENKeyword + '%') on EN.JBENsiteID = AD.JBASiteID
ORDER BY AD.JBAID DESC
ASKER CERTIFIED SOLUTION
lof

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros