Help:Very hard query with duplicates doesn't work

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 2008 Forums

Transact-SQL (2008)

Help:Very hard query with duplicates doesn't work

Author

Topic

olivierb
Starting Member

6 Posts

Posted - 2014-01-14 : 02:46:12

Dear Friends,

First, I say hello to everyone in this forum. My name is Olivier and I work in Lausanne / Switzerland, in the french part of the country. I am not an DB Admin, but need to progress in this way.

I Made a SELECT query that works perfectly. Here is it:

SELECT
tblBusinessApplication.Name AS NOM, tblBusinessApplication.ApplicationID AS 'ID Business Application', tblApplicationLayer.ApplicationLayerCode AS Catégorie,
tblPackageApplication.Editor AS Editeur, tblPackageApplication.Application AS 'Référence Editeur', tblPackageApplication.Name AS Logiciels,
tblPackageApplication.ID AS 'Logiciel ID Package', tblPackageApplication.Version, tblBusinessApplication.Comment AS Commentaires,
tblPackageApplication.Comment AS 'Commentaire package', tblActor_1.Name AS Contact,
tblActor.Name AS 'Chef De Projet',
tblMode.StatusDate,
tblBusinessPackage.TypeBusinessApplication

FROM
tblPackageApplication FULL OUTER JOIN
tblBusinessApplication FULL OUTER JOIN
tblApplicationLayer ON tblBusinessApplication.ApplicationLayerID = tblApplicationLayer.ApplicationLayerID FULL OUTER JOIN
tblBusinessPackage ON tblBusinessApplication.ApplicationID = tblBusinessPackage.ApplicationID ON
tblPackageApplication.ID = tblBusinessPackage.PackageApplication FULL OUTER JOIN
tblPackaging ON tblPackageApplication.ID = tblPackaging.PackageApplicationID FULL OUTER JOIN
tblActor ON tblBusinessApplication.ProjectManagerActorId = tblActor.ID FULL OUTER JOIN
tblActor AS tblActor_1 ON tblBusinessApplication.ContactActorID = tblActor_1.ID FULL OUTER JOIN
tblMode ON tblPackaging.ID = tblMode.PackagingID

WHERE
(tblPackageApplication.Comment NOT LIKE '%obsolète%') AND
(tblBusinessApplication.RichClient = 'oui') AND
(tblBusinessApplication.Win7Scope = 'oui') AND
(tblPackageApplication.Version NOT LIKE 'X.X') AND
(tblBusinessPackage.TypeBusinessApplication = 'p')

ORDER BY tblBusinessApplication.ApplicationID

So as I said it works, but I have few duplicates in tblPackageApplication.Name wich I need to remove

EXEMPLE:

Adobe Acrobat 10
Office 2010 PRO SP2
Adobe Acrobat 10

So I try to remove that using

AND (((tblPackageApplication.Name) In (SELECT [Name] FROM [tblPackageApplication] As Tmp GROUP BY [Name] HAVING Count(*)>1 )))

So the new query is

SELECT
tblBusinessApplication.Name AS NOM, tblBusinessApplication.ApplicationID AS 'ID Business Application', tblApplicationLayer.ApplicationLayerCode AS Catégorie,
tblPackageApplication.Editor AS Editeur, tblPackageApplication.Application AS 'Référence Editeur', tblPackageApplication.Name AS Logiciels,
tblPackageApplication.ID AS 'Logiciel ID Package', tblPackageApplication.Version, tblBusinessApplication.Comment AS Commentaires,
tblPackageApplication.Comment AS 'Commentaire package', tblActor_1.Name AS Contact, tblActor.Name AS 'Chef De Projet', tblMode.StatusDate,
tblBusinessPackage.TypeBusinessApplication

FROM
tblPackageApplication FULL OUTER JOIN
tblBusinessApplication FULL OUTER JOIN
tblApplicationLayer ON tblBusinessApplication.ApplicationLayerID = tblApplicationLayer.ApplicationLayerID FULL OUTER JOIN
tblBusinessPackage ON tblBusinessApplication.ApplicationID = tblBusinessPackage.ApplicationID ON
tblPackageApplication.ID = tblBusinessPackage.PackageApplication FULL OUTER JOIN
tblPackaging ON tblPackageApplication.ID = tblPackaging.PackageApplicationID FULL OUTER JOIN
tblActor ON tblBusinessApplication.ProjectManagerActorId = tblActor.ID FULL OUTER JOIN
tblActor AS tblActor_1 ON tblBusinessApplication.ContactActorID = tblActor_1.ID FULL OUTER JOIN
tblMode ON tblPackaging.ID = tblMode.PackagingID

WHERE
(tblPackageApplication.Comment NOT LIKE '%obsolète%') AND
(tblBusinessApplication.RichClient = 'oui') AND
(tblBusinessApplication.Win7Scope = 'oui') AND
(tblPackageApplication.Version NOT LIKE 'X.X') AND
(tblBusinessPackage.TypeBusinessApplication = 'p')
AND (((tblPackageApplication.Name) In (SELECT [Name] FROM [tblPackageApplication] As Tmp GROUP BY [Name] HAVING Count(*)>1 )))

ORDER BY tblBusinessApplication.ApplicationID

But it returns nothing, but I have NO Syntaxe errors.

So I looked the Net for few days to try how to deal with this problem and tried severa things, without success.

So if anyone has an idea, it will be welcomed.

Best regards,

Olivier

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-14 : 03:30:24

--If tblPackageApplication.ID is unique, you can remove row having dupulicate name from tblPackageApplication.

SELECT
	Editor,
	Application,
	Name,
	ID,
	Version,
	Comment
FROM (
	SELECT
		ROW_NUMBER() OVER(PARTITION BY tblPackageApplication.Name ORDER BY tblPackageApplication.ID) RowNum,
		*
	FROM tblPackageApplication)
WHERE RowNum = 1;

--So, as one of way, replace tblPackageApplication by query like following.

WITH newTblPackageApplication (Editor, Application, Name, ID, Version, Comment) AS (
SELECT
	Editor,
	Application,
	Name,
	ID,
	Version,
	Comment
FROM (
	SELECT
		ROW_NUMBER() OVER(PARTITION BY tblPackageApplication.Name ORDER BY tblPackageApplication.ID) RowNum,
		*
	FROM tblPackageApplication)
WHERE RowNum = 1
)
SELECT
tblBusinessApplication.Name AS NOM,
tblBusinessApplication.ApplicationID AS 'ID Business Application',
tblApplicationLayer.ApplicationLayerCode AS Catégorie,
newTblPackageApplication.Editor AS Editeur,
newTblPackageApplication.Application AS 'Référence Editeur',
newTblPackageApplication.Name AS Logiciels,
newTblPackageApplication.ID AS 'Logiciel ID Package',
newTblPackageApplication.Version,
tblBusinessApplication.Comment AS Commentaires,
newTblPackageApplication.Comment AS 'Commentaire package',
tblActor_1.Name AS Contact,
tblActor.Name AS 'Chef De Projet',
tblMode.StatusDate,
tblBusinessPackage.TypeBusinessApplication

FROM newTblPackageApplication
FULL OUTER JOIN tblBusinessApplication
FULL OUTER JOIN tblApplicationLayer
	ON tblBusinessApplication.ApplicationLayerID = tblApplicationLayer.ApplicationLayerID
FULL OUTER JOIN tblBusinessPackage
	ON tblBusinessApplication.ApplicationID = tblBusinessPackage.ApplicationID
	ON newTblPackageApplication.ID = tblBusinessPackage.PackageApplication
FULL OUTER JOIN tblPackaging
	ON newTblPackageApplication.ID = tblPackaging.PackageApplicationID
FULL OUTER JOIN tblActor
	ON tblBusinessApplication.ProjectManagerActorId = tblActor.ID
FULL OUTER JOIN tblActor AS tblActor_1
	ON tblBusinessApplication.ContactActorID = tblActor_1.ID
FULL OUTER JOIN tblMode
	ON tblPackaging.ID = tblMode.PackagingID

WHERE
(newTblPackageApplication.Comment NOT LIKE '%obsolète%') AND
(tblBusinessApplication.RichClient = 'oui') AND
(tblBusinessApplication.Win7Scope = 'oui') AND
(newTblPackageApplication.Version NOT LIKE 'X.X') AND
(tblBusinessPackage.TypeBusinessApplication = 'p')

ORDER BY tblBusinessApplication.ApplicationID

--I hope this will help.

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 04:28:20

you could simply do it inline without any temp table or CTE


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tblPackageApplication.Name  ORDER BY tblPackageApplication.Name) AS Seq,
tblBusinessApplication.Name AS NOM, tblBusinessApplication.ApplicationID AS 'ID Business Application', tblApplicationLayer.ApplicationLayerCode AS Catégorie, 
tblPackageApplication.Editor AS Editeur, tblPackageApplication.Application AS 'Référence Editeur', tblPackageApplication.Name AS Logiciels, 
tblPackageApplication.ID AS 'Logiciel ID Package', tblPackageApplication.Version, tblBusinessApplication.Comment AS Commentaires, 
tblPackageApplication.Comment AS 'Commentaire package', tblActor_1.Name AS Contact, tblActor.Name AS 'Chef De Projet', tblMode.StatusDate, 
tblBusinessPackage.TypeBusinessApplication

FROM 
tblPackageApplication FULL OUTER JOIN
tblBusinessApplication FULL OUTER JOIN
tblApplicationLayer ON tblBusinessApplication.ApplicationLayerID = tblApplicationLayer.ApplicationLayerID FULL OUTER JOIN
tblBusinessPackage ON tblBusinessApplication.ApplicationID = tblBusinessPackage.ApplicationID ON 
tblPackageApplication.ID = tblBusinessPackage.PackageApplication FULL OUTER JOIN
tblPackaging ON tblPackageApplication.ID = tblPackaging.PackageApplicationID FULL OUTER JOIN
tblActor ON tblBusinessApplication.ProjectManagerActorId = tblActor.ID FULL OUTER JOIN
tblActor AS tblActor_1 ON tblBusinessApplication.ContactActorID = tblActor_1.ID FULL OUTER JOIN
tblMode ON tblPackaging.ID = tblMode.PackagingID

WHERE 
(tblPackageApplication.Comment NOT LIKE '%obsolète%') AND 
(tblBusinessApplication.RichClient = 'oui') AND 
(tblBusinessApplication.Win7Scope = 'oui') AND 
(tblPackageApplication.Version NOT LIKE 'X.X') AND 
(tblBusinessPackage.TypeBusinessApplication = 'p') 
)t
WHERE Seq=1
ORDER BY ApplicationID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

olivierb
Starting Member

6 Posts

Posted - 2014-01-14 : 05:56:59

quote:
Originally posted by nagino

--I hope this will help.

-------------------------------------
From Japan
Sorry, my English ability is limited.

Dear Nagino,

I understand the concept, mais it makes an incorrect syntax near the keyword WHERE RowNum = 1

Here are the picture

http://cjoint.com/?0Aol3tMJid8

Best regards,

Olivier

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 07:21:10

quote:
Originally posted by olivierb

quote:
Originally posted by nagino

--I hope this will help.

-------------------------------------
From Japan
Sorry, my English ability is limited.

Dear Nagino,

I understand the concept, mais it makes an incorrect syntax near the keyword WHERE RowNum = 1

Here are the picture

http://cjoint.com/?0Aol3tMJid8

Best regards,

Olivier

you're missing an alias for derived table hence the error
use like below and it will work.


SELECT
	Editor,
	Application,
	Name,
	ID,
	Version,
	Comment
FROM (
	SELECT
		ROW_NUMBER() OVER(PARTITION BY tblPackageApplication.Name ORDER BY tblPackageApplication.ID) RowNum,
		*
	FROM tblPackageApplication)t
WHERE RowNum = 1;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

olivierb
Starting Member

6 Posts

Posted - 2014-01-14 : 07:46:44

Thanks alot Visakh - I just put the letter - t - and it works ;>)

But I do not understand what to the letter t refer... You say it is an alias, from from where ?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-01-14 : 07:49:26

The letter t is the alias for the query within the parenthesis.

djj

olivierb
Starting Member

6 Posts

Posted - 2014-01-14 : 07:57:17

Dear Visakh and dear Nagino, thanks alot for your valuable help, both scripts a materpieces and will help me a lot to study SQL !

It 'is nice that we visited India and Japan few years ago and we had alot of pleasure. This year will go to New York ;>)

Case closed

best regards,

Olivier

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-14 : 18:33:10

Oh, sorry for the mistake, Olivierb. This case need alias.
Thanks for followup, Visakh.

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:16:41

quote:
Originally posted by olivierb

Dear Visakh and dear Nagino, thanks alot for your valuable help, both scripts a materpieces and will help me a lot to study SQL !

It 'is nice that we visited India and Japan few years ago and we had alot of pleasure. This year will go to New York ;>)

Case closed

best regards,

Olivier

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:17:07

quote:
Originally posted by nagino

Oh, sorry for the mistake, Olivierb. This case need alias.
Thanks for followup, Visakh.

-------------------------------------
From Japan
Sorry, my English ability is limited.

No problem mate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources