Help with MS SQL Query

Help!

I'm building a database, which stores jobs, and users, users can set up alerts to get notified about jobs. I want the alerts to run daily.

The process should be;

List all distinct job sector, region, location combinations for the day
Select all users with alerts that match the selection above
I've written the SQL to select all distinct combinations for the day, which is

SELECT 
   [Region], [RegionID], [Location], [LocationID], 
   [Sector], [SectorID]
FROM 
   [JBR].[dbo].[JBN]
WHERE 
   [DatePosted] >= DATEADD(d,-1,GETDATE())
GROUP BY 
   [RegionID], [LocationID], [SectorID]
HAVING 
   COUNT(*) >= 1
ORDER BY 
   [LocationID]
GO

Open in new window


My select statement to select the users looks a little like;

SELECT DISTINCT 
    (C.JBACAID),
    C.JBACAUsername
FROM
    dbo.JBACandidate C 
INNER JOIN 
    dbo.JBEmailNotification AD ON AD.JBENcandidateID = C.JBACAID 
WHERE
    JBENlocation = ???? OR JBENlocation IS NULL
    AND JBENlocation = ???? OR JBENlocation IS NULL
    AND JBENcategory = ???? OR JBENcategory IS NULL

Open in new window


The output should be that for each distinct sector region location combination of the day I get a recordset containing;

[JBACAID]
[JBACAUsername]
[Region]
[RegionID]
[Location]
[LocationID]
[Sector]
[SectorID]

Any suggestions on how to build this query?

Thank you
garethtnashAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Without knowing the model and an incomplete example. it's hard to tell. But when I need to guess:

WITH Jobs AS 
	(
		SELECT	[Region], [RegionID], [Location], [LocationID], [Sector], [SectorID]
		FROM	[JBR].[dbo].[JBN]
		WHERE	[DatePosted] >= DATEADD(d,-1,GETDATE())
		GROUP BY [RegionID], [LocationID], [SectorID]
		HAVING COUNT(*) >= 1
	),
	Users AS 
	(
		SELECT DISTINCT C.JBACAID, C.JBACAUsername, JBENlocation
		FROM	dbo.JBACandidate C 
			INNER JOIN dbo.JBEmailNotification AD ON AD.JBENcandidateID = C.JBACAID 
	)
	SELECT	*
	FROM	Jobs J
		INNER JOIN Users U ON J.Location = U.JBENlocation;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Thanks Ste5an, can you explain the code?
ste5anSenior DeveloperCommented:
It's simply a common table expression (CTE). It's equivalent to using sub-queries like

SELECT	*
FROM	(	SELECT	[Region], [RegionID], [Location], [LocationID], [Sector], [SectorID]
		FROM	[JBR].[dbo].[JBN]
		WHERE	[DatePosted] >= DATEADD(d,-1,GETDATE())
		GROUP BY [RegionID], [LocationID], [SectorID]
		HAVING COUNT(*) >= 1	
	) J
	INNER JOIN (	SELECT DISTINCT C.JBACAID, C.JBACAUsername, JBENlocation
			FROM	dbo.JBACandidate C 
				INNER JOIN dbo.JBEmailNotification AD ON AD.JBENcandidateID = C.JBACAID 
			) U ON J.Location = U.JBENlocation;

Open in new window

garethtnashAuthor Commented:
Cool thank you
garethtnashAuthor Commented:
Excellent - thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.