Complicated Select & Insert statement - MS SQL 2008

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

Open in new window

garethtnashAsked:
Who is Participating?
 
lofCommented:
To be frank, you explanation does not much sense but I had a look at the queries you provided and that's something I came up with. Try it and see if the result is what you are looking for

 Probably the sub-query could be improved but for that I would need to know all the relations and understand why you add this line in:

Where JBENlocation IN(SELECT JBLocation FROM dbo.JBLocation WHERE JBLID = 435)


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,
(
	Select 
	Count(distinct C.JBACAUsername) 
	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 + '%' 
) 
FROM dbo.JBAdvert AD 
inner join dbo.JBLocation JL
on AD.JBALocation = JL.JBLocation and JL.JBLID = @LocationId
ORDER BY AD.JBAID DESC

Open in new window

0
 
garethtnashAuthor Commented:
Hi Lof,
Thanks, that looks really good, I've changed it slightly and will test it now -
Thank you

CREATE PROCEDURE [dbo].[JobboardBBCountEmailRecipients]
        @LocationID int,
        @JobTitle nvarchar(50),
        @category nvarchar(50),
        @username nvarchar(255),
        @description nvarchar(max)
AS

INSERT INTO dbo.JBESent (JBESJobID, JBESSiteID, JBESLocation, JBESCategory, JBESReceipients)
Select Top (1)
AD.JBAID,
AD.JBASiteID, 
AD.JBALocation, 
AD.JBACategory,
(
	Select 
	Count(distinct EN.JBENcandidateID) 
	from dbo.JBEmailNotification EN 
	inner join dbo.JBLocation L on L.JBLSiteID = EN.JBENsiteID
	Where JBENlocation IN(SELECT JBLocation FROM dbo.JBLocation WHERE JBLID = @LocationID) 
	AND JBENcategory = @category AND @description like '%' + JBENKeyword + '%' 
) 
FROM dbo.JBAdvert AD 
Where JBAEmployeeID IN (Select JBEID from JBEmployee E inner join dbo.JBLocation L on L.JBLSiteID = E.JBESiteID Where E.JBEUsername = @username)
ORDER BY AD.JBAID DESC

Open in new window

0
 
lofCommented:
Usually it is good to replace sub-queries with joins if it is possible as usually it gives better performance.
0
 
aplusexpertCommented:
Please try this.


Select top 1 JBAdvert JBAID, JBASiteID, JBALocation, JBACategory From JBAdvert inner join
JBAdvert JBALocation = (JBLocation JBLocation) and  JBLocation.JBLID = @LocationID
Left outer join  JBEmployee  on  JBAdvert  JBASiteID = JBEmployee  JBESiteID
Where JBEmployee JBEUsername = @username order by JBAdvert. JBAID desc
0
 
garethtnashAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.