Urgent Help with SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Urgent Help with SQL Query

Author

Topic

panayiotis
Starting Member

16 Posts

Posted - 2008-08-29 : 17:37:28

Hello,

I have the followin query in a store procedure and when i run it it return dublicate rows.

SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(ORDER BY JOB.PostDate DESC, JOB.JobID DESC) as RowNum
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (JOB.Title Like '%a%')

If i remove the ROW_NUMBER() OVER(ORDER BY JOB.PostDate DESC, JOB.JobID DESC) as RowNum it works fine but the problem is that ROW_NUMBER must be in the querry.

Can you please suggest what may be the problem.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-29 : 18:30:33

You haven't provided enough information for us to help you with this. Sample data with expected result set would be very helpful.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-08-29 : 18:41:16

Wouldn't the ROW_NUMBER make each "repeating" row unique?

Is there a way to use derived tables to get a "unique" record set and join it to your ROW_NUMBER information? something like:

SELECT a.*, b.RowNum
from
(select distinct blah, blah
from yadda
where whatever
) a
inner join
(select ROW_NUMBER, keycol
from yadda
) b
on a.keycol = b.keycol

=======================================
Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:25:57

quote:
Originally posted by panayiotis

Hello,

I have the followin query in a store procedure and when i run it it return dublicate rows.

SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(ORDER BY JOB.PostDate DESC, JOB.JobID DESC) as RowNum
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (JOB.Title Like '%a%')

If i remove the ROW_NUMBER() OVER(ORDER BY JOB.PostDate DESC, JOB.JobID DESC) as RowNum it works fine but the problem is that ROW_NUMBER must be in the querry.

Can you please suggest what may be the problem.

Thanks in advance.

Can you explain what distinct values you're trying to retrive? Also why is unique row number generated if you're looking for distinct values? Please explain your full requirement with some sample data and show us your expected output out of them also.

panayiotis
Starting Member

16 Posts

Posted - 2008-08-30 : 03:23:16

Basically i am trying to make a store procedure to return paged results.

This is a simple example of such procedure:

CREATE PROCEDURE [dbo].[Article_GetALL]
@startRowIndex int=0,
@maximumRows int=-1

AS
BEGIN

IF @maximumRows is null OR @maximumRows < 1
BEGIN
SELECT * FROM Article ORDER BY ArticleID desc
End
ELSE
BEGIN

SET @startRowIndex = @startRowIndex + 1

SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY ArticleID desc) as RowNum
FROM Article
) As TableA
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
END
END

The other one is a bit more complecated as it has to do some filtering as well as some sorting. The tables involved are:

CREATE TABLE [dbo].[Job](
[JobID] [int] NOT NULL,
[RecruiterID] [int] NOT NULL,
[JobTypeID] [int] NOT NULL,
[PriorityID] [int] NOT NULL,
[Department] [nvarchar](150) NULL,
[Title] [nvarchar](250) NOT NULL,
[Description] [nvarchar](max) NOT NULL
CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED
(
[JobID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Job] WITH NOCHECK ADD CONSTRAINT [FK_Job_JobType] FOREIGN KEY([JobTypeID])
REFERENCES [dbo].[JobType] ([JobTypeID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_JobType]
GO
ALTER TABLE [dbo].[Job] WITH NOCHECK ADD CONSTRAINT [FK_Job_Priority] FOREIGN KEY([PriorityID])
REFERENCES [dbo].[Priority] ([PriorityID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_Priority]
GO
ALTER TABLE [dbo].[Job] WITH NOCHECK ADD CONSTRAINT [FK_Job_Recruiter] FOREIGN KEY([RecruiterID])
REFERENCES [dbo].[Recruiter] ([RecruiterID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_Recruiter]

--------------------

CREATE TABLE [dbo].[JobType](
[JobTypeID] [int] NOT NULL,
[Name] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_JobType] PRIMARY KEY CLUSTERED
(
[JobTypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

---------------------

CREATE TABLE [dbo].[JobSector](
[JobSectorID] [int] NOT NULL,
[JobID] [int] NOT NULL,
[SectorID] [int] NOT NULL,
CONSTRAINT [PK_JobSector] PRIMARY KEY CLUSTERED
(
[JobSectorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-------------------------------

CREATE TABLE [dbo].[Sector](
[SectorID] [int] NOT NULL,
[ParentID] [int] NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [nvarchar](500) NULL,
[Keywords] [nvarchar](500) NULL,
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[SectorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Sector] WITH NOCHECK ADD CONSTRAINT [FK_Sector_Sector] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Sector] ([SectorID])
GO
ALTER TABLE [dbo].[Sector] CHECK CONSTRAINT [FK_Sector_Sector]

---------------------------------

CREATE TABLE [dbo].[JobLocation](
[JobLocationID] [int] NOT NULL,
[JobID] [int] NOT NULL,
[LocationID] [int] NOT NULL,
CONSTRAINT [PK_JobLocation] PRIMARY KEY CLUSTERED
(
[JobLocationID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[JobLocation] WITH NOCHECK ADD CONSTRAINT [FK_JobLocation_Job] FOREIGN KEY([JobID])
REFERENCES [dbo].[Job] ([JobID])
GO
ALTER TABLE [dbo].[JobLocation] CHECK CONSTRAINT [FK_JobLocation_Job]
GO
ALTER TABLE [dbo].[JobLocation] WITH NOCHECK ADD CONSTRAINT [FK_JobLocation_Location] FOREIGN KEY([LocationID])
REFERENCES [dbo].[Location] ([LocationID])
GO
ALTER TABLE [dbo].[JobLocation] CHECK CONSTRAINT [FK_JobLocation_Location]

---------------------------------------

CREATE TABLE [dbo].[Location](
[LocationID] [int] NOT NULL,
[ParentID] [int] NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Location] WITH NOCHECK ADD CONSTRAINT [FK_Location_Location] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Location] ([LocationID])
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Location]

--------------------------------

Couble of other tables involved as well; COMPANYPROFILE, RECRUITERPACKAGE and PACKAGE but these dont affect my qry.

As you can see in the statement:
SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(ORDER BY JOB.PostDate DESC, JOB.JobID DESC) as RowNum
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (JOB.Title Like '%a%')

I am trying to join the above tables and page the result as the sample store procedure does. but it returns dublicated rows because one job record can be associated with multible locations and multible job sectors. I dont know if i remove the PK from the joblocation and the jobsector if that makes any difference. I know that there i break normalization but it is not that important.

The final store procedure looks something like this:

CREATE PROCEDURE [dbo].[Job_AdvanceSearch]
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@SectorIDFilter varchar(50),
@KeywordsFilter varchar(max),
@SortBy varchar(max),
@startRowIndex int=0,
@maximumRows int=-1
AS
BEGIN

DECLARE @SQL nvarchar(max)

IF @maximumRows is null OR @maximumRows < 1
BEGIN
SET @SQL = 'SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (' + @KeywordsFilter + ')'

IF @TypeIDFilter IS NOT NULL AND @TypeIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JOB.JOBTYPEID IN (' + @TypeIDFilter + ')'
END

IF @LocationIDFilter IS NOT NULL AND @LocationIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JobLocation.LocationID IN (' + @LocationIDFilter + ')'
END

IF @SectorIDFilter IS NOT NULL AND @SectorIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND Sector.SectorID IN (SELECT SectorID FROM SECTOR WHERE SECTORID IN (' + @SectorIDFilter + ') OR PARENTID IN (' + @SectorIDFilter + '))'
END

SET @SQL = @SQL + @SortBy
End
ELSE
BEGIN

SET @startRowIndex = @startRowIndex + 1

SET @SQL = 'SELECT * FROM
(
SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(' + @SortBy + ') as RowNum
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (' + @KeywordsFilter + ')'

IF @TypeIDFilter IS NOT NULL AND @TypeIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JOB.JOBTYPEID IN (' + @TypeIDFilter + ')'
END

IF @LocationIDFilter IS NOT NULL AND @LocationIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JobLocation.LocationID IN (' + @LocationIDFilter + ')'
END

IF @SectorIDFilter IS NOT NULL AND @SectorIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND Sector.SectorID IN (SELECT SectorID FROM SECTOR WHERE SECTORID IN (' + @SectorIDFilter + ') OR PARENTID IN (' + @SectorIDFilter + '))'
END

SET @SQL = @SQL +

') As TableA
WHERE RowNum BETWEEN ' + (Convert(varchar(8000), @startRowIndex)) + ' AND (' + (Convert(varchar(8000), (@startRowIndex + @maximumRows))) + ') - 1'

END
PRINT @SQL
EXEC sp_executesql @SQL

END

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 03:49:53

if you want 1 record per jobid, then group by jobid while retrieving result. ALso in that case you can retrive only a single value for other fields so use MIN() or MAX() over them.

ALso you dont need the if else condition you just need this

SET @SQL = 'SELECT * FROM 
(
SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(' + @SortBy + ') as RowNum 
FROM 
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (' + @KeywordsFilter + ')' 

IF @TypeIDFilter IS NOT NULL AND @TypeIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JOB.JOBTYPEID IN (' + @TypeIDFilter + ')'
END

IF @LocationIDFilter IS NOT NULL AND @LocationIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JobLocation.LocationID IN (' + @LocationIDFilter + ')'
END 

IF @SectorIDFilter IS NOT NULL AND @SectorIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND Sector.SectorID IN (SELECT SectorID FROM SECTOR WHERE SECTORID IN (' + @SectorIDFilter + ') OR PARENTID IN (' + @SectorIDFilter + '))'
END 

SET @SQL = @SQL +

') As TableA 
WHERE (RowNum BETWEEN ' + (Convert(varchar(8000), @startRowIndex)) + ' AND (' + (Convert(varchar(8000), (@startRowIndex + @maximumRows))) + ') - 1) OR (@maximumRows is null OR @maximumRows < 1)' 

END
...

how will the values for @KeywordsFilter be passed?

panayiotis
Starting Member

16 Posts

Posted - 2008-08-30 : 04:30:38

quote:

Originally posted by visakh16

if you want 1 record per jobid, then group by jobid while retrieving result. ALso in that case you can retrive only a single value for other fields so use MIN() or MAX() over them.

ALso you dont need the if else condition you just need this

SET @SQL = 'SELECT * FROM 
(
SELECT DISTINCT JOB.*, (SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=JOB.RECRUITERID AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
JOBTYPE.Name AS JOBTYPENAME, Sector.Name, ROW_NUMBER() OVER(' + @SortBy + ') as RowNum 
FROM 
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (' + @KeywordsFilter + ')' 

IF @TypeIDFilter IS NOT NULL AND @TypeIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JOB.JOBTYPEID IN (' + @TypeIDFilter + ')'
END

IF @LocationIDFilter IS NOT NULL AND @LocationIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JobLocation.LocationID IN (' + @LocationIDFilter + ')'
END 

IF @SectorIDFilter IS NOT NULL AND @SectorIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND Sector.SectorID IN (SELECT SectorID FROM SECTOR WHERE SECTORID IN (' + @SectorIDFilter + ') OR PARENTID IN (' + @SectorIDFilter + '))'
END 

SET @SQL = @SQL +

') As TableA 
WHERE (RowNum BETWEEN ' + (Convert(varchar(8000), @startRowIndex)) + ' AND (' + (Convert(varchar(8000), (@startRowIndex + @maximumRows))) + ') - 1) OR (@maximumRows is null OR @maximumRows < 1)' 

END
...

how will the values for @KeywordsFilter be passed?

The call looks something like this:

[Job_AdvanceSearch] '%', '%', '%', 'Title like ''%''', 'ORDER BY JOB.JOBID', 0,10

I will be calling this from code.

This is now works;
ALTER PROCEDURE [dbo].[Job_AdvanceSearch]
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@SectorIDFilter varchar(50),
@KeywordsFilter varchar(max),
@SortBy varchar(max),
@startRowIndex int=0,
@maximumRows int=-1
AS
BEGIN

DECLARE @SQL nvarchar(max)

SET @startRowIndex = @startRowIndex + 1

SET @SQL = 'SELECT * FROM
(
SELECT DISTINCT MAX(JOB.JobID) AS JobID,
MAX(JOB.RecruiterID) AS RecruiterID,
MAX(JOB.JobTypeID) AS JobTypeID,
MAX(JOB.PriorityID) AS PriorityID,
MAX(JOB.Department) AS Department,
MAX(JOB.Title) AS Title,
MAX(JOB.Description) AS Description,
MAX(JOB.CandidatesRequired) AS CandidatesRequired,
MAX(JOB.MinSalary) AS MinSalary,
MAX(JOB.MaxSalary) AS MaxSalary,
MAX(JOB.DisplaySalary) AS DisplaySalary,
MAX(JOB.Benefits) AS Benefits,
MAX(JOB.StartDate) AS StartDate,
MAX(JOB.Duration) AS Duration,
MAX(JOB.Reference) AS Reference,
MAX(JOB.PostDate) AS PostDate,
MAX(JOB.ExpiredDate) AS ExpiredDate,
MAX(JOB.Comments) AS Comments,
(SELECT COMPANYLOGO FROM COMPANYPROFILE WHERE RECRUITERID=max(JOB.RECRUITERID) AND RECRUITERID IN (SELECT RECRUITERID FROM RECRUITERPACKAGE WHERE PACKAGEID IN (SELECT PACKAGEID FROM PACKAGE WHERE DISPLAYLOGO=1))) AS LOGO,
MAX(JOBTYPE.Name) AS JOBTYPENAME,
MAX(Sector.Name) AS SectorName,
ROW_NUMBER() OVER(' + @SortBy + ') as RowNum
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE (' + @KeywordsFilter + ')'

IF @TypeIDFilter IS NOT NULL AND @TypeIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JOB.JOBTYPEID IN (' + @TypeIDFilter + ')'
END

IF @LocationIDFilter IS NOT NULL AND @LocationIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND JobLocation.LocationID IN (' + @LocationIDFilter + ')'
END

IF @SectorIDFilter IS NOT NULL AND @SectorIDFilter <> '%'
BEGIN
SET @SQL = @SQL + 'AND Sector.SectorID IN (SELECT SectorID FROM SECTOR WHERE SECTORID IN (' + @SectorIDFilter + ') OR PARENTID IN (' + @SectorIDFilter + '))'
END
SET @SQL=@SQL + ' GROUP BY JOB.JOBID'
SET @SQL = @SQL +

') As TableA'
-- WHERE (RowNum BETWEEN ' + (Convert(varchar(8000), @startRowIndex)) + ' AND (' + (Convert(varchar(8000), (@startRowIndex + @maximumRows))) + ') - 1) OR (' + @maximumRows + ' is null OR ' + @maximumRows + ' < 1)'

PRINT @SQL
EXEC sp_executesql @SQL

END

I am a bit concern with the performance of this querry on a big database. Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 05:14:24

Why do you want the fileter condition to send like this?Title like ''%''', 'ORDER BY JOB.JOBID'...
You could simply put the parameters for each field and also sortyby parameter where you pass only field name. in such case you can dispense with current dynamic sql and use a simple case construct for your filter.

panayiotis
Starting Member

16 Posts

Posted - 2008-08-30 : 06:32:59

quote:
Originally posted by visakh16

Why do you want the fileter condition to send like this?Title like ''%''', 'ORDER BY JOB.JOBID'...
You could simply put the parameters for each field and also sortyby parameter where you pass only field name. in such case you can dispense with current dynamic sql and use a simple case construct for your filter.

Hmmm i am not sure if its going to work. I will give it a try.

The other half of the problem has a similar issue now. The other part is a querry which should return the number of records the original querry returns.

It looks like this:
SELECT COUNT(*)
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE JOB.APPROVED=1
GROUP BY JOB.JOBID

The result is:
(No Column Name)
2
2
2
3
4

Where as it should be 5. If i remove the GROUP BY JOB.JOBID the result is 13 because some jobs have more than one location and industry sector associated with them. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 10:17:12

[code]SELECT COUNT(DISTINCT JOB.JOBID)
FROM
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE JOB.APPROVED=1
~~GROUP BY JOB.JOBID~~[/code]

panayiotis
Starting Member

16 Posts

Posted - 2008-08-30 : 11:46:27

quote:

Originally posted by visakh16

SELECT COUNT(DISTINCT JOB.JOBID) 
FROM 
JOB INNER JOIN (JobSector INNER JOIN Sector ON JobSector.SectorID=Sector.SectorID)
ON JOB.JOBID=JobSector.JOBID INNER JOIN JobType ON JOB.JobTypeID=JOBTYPE.JobTypeID
INNER JOIN JobLocation ON JobLocation.JOBID=JOB.JOBID
WHERE JOB.APPROVED=1
GROUP BY JOB.JOBID

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources