asked on

Recordset with joins not working

Hello All,

Small problem with one of my recordset falling down, its multiplying the results from two of the joined tables....

My tables (& columns) are -

dbo.JBAdvert
JBAID
JBATitle
JBADescription
JBAPayRate
JBALocation
JBADatePosted
JBACategory

dbo.JBApplication
JBAPID
JBAPAdvertID

dbo.JBVacancyview
VacancyID
ID

dbo.JBClient
JBCLID
JBCLName
JBCLLogo

I think you can see from below, what I am trying to do, the problem is with COUNT(AV.ID) AS ADViews, COUNT(AP.JBAPID) AS Applications, in that if there have been any applications, it appears to multiply the two values.

Any thoughts really appreciated, .. thank you

Select TOP(40)
AD.JBAID, Lower(AD.JBATitle) AS JBATitle, AD.JBALocation, AD.JBACategory, AD.JBAPayRate, AD.JBADatePosted, Lower(left(AD.JBADescription,300)+'...') as JBADescription, CL.JBCLName, CL.JBCLLogo, COUNT(AV.ID) AS ADViews, COUNT(AP.JBAPID) AS Applications
FROM dbo.JBAdvert AD left join dbo.JBApplication AP on AP.JBAPAdvertID = AD.JBAID left join dbo.JBVacancyview AV on AV.VacancyID = AD.JBAID inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
Where AD.JBASiteID = @SiteID AND JBADatePosted >= DATEADD(d,-JBAPostFor,GETDATE())
GROUP BY AD.JBAID, AD.JBATitle, AD.JBALocation, AD.JBACategory, AD.JBAPayRate, AD.JBADatePosted, AD.JBADescription, CL.JBCLName, CL.JBCLLogo
ORDER BY AD.JBADatePosted DESC

Open in new window

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Very neat thank you CG,
I hope you don't mind, but it was a bit of a starter question, in that now the recordset works properly, my intention was to run it as an SP instead, with a few more variables etc, as below...
The problem is that my SP looks completely wrong, your thoughts would be really appreciated.

Once again thank you.

Gareth

CREATE PROCEDURE [dbo].[newdesignjobresults]
(
@JBASiteID integer,
@JBALocation varchar(255),
@JBACategory varchar(255),
@JBADescription varchar(255)
)
 
AS
BEGIN
IF EXISTS
    (
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        1 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
      AND (@JBALocation Is Null Or JBALocation = @JBALocation)
      AND (@JBACategory Is Null Or JBACategory = @JBACategory) 
      AND (@JBADescription Is Null OR JBADescription LIKE '%' + @JBADescription + '%')
ORDER BY AD.JBADatePosted DESC
    )
    ELSE
(
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        2 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
ORDER BY AD.JBADatePosted DESC
) 
END
 
GO

Open in new window

garethtnash

ASKER

Thank you

Chris Luttrell

Gareth, it looks like you opened another question and already got an answer. If not, and in the future, if you use the "ask a related question" link in the top of the Post Comment box to pose a follow on question, the person(s) that contributed to this one will get an email notification about the new question.
Thanks,
Chris