[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Complicated Select & Insert statement - MS SQL 2008

Posted on 2009-12-18
5
Medium Priority
?
298 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:garethtnash
  • 2
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
lof earned 1600 total points
ID: 26082485
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
 

Author Comment

by:garethtnash
ID: 26082770
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
 
LVL 10

Expert Comment

by:lof
ID: 26082959
Usually it is good to replace sub-queries with joins if it is possible as usually it gives better performance.
0
 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 400 total points
ID: 26086011
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
 

Author Closing Comment

by:garethtnash
ID: 31667833
Thank you
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question