Need help with script - 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)

Need help with script

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2009-06-30 : 03:26:39

Hello,

i am having a table like this :
name desc
a1 1
a1 2
a1 3

b1 1
b1 2
b1 3
b1 6
b1 7

c1 1
c1 2

d1 1
d1 2
d1 3
d1 4
d1 5
d1 6
etc

what i would like to do in a script is the following :

return the name(s) where all the descriptions of a1 are found in all the collumn name. (all 3 of them must exist in the other else there should not be any value returned). also if more names contain 1,2,3 then a list of them would be perfect. it doesn't matter if names contain more than 1,2,3. the condidition should be at least them.

The result from the upper table example should be :
------
b1
d1
------

i really have problems thinking this one and i would apreciate any help. of course the table is quite big reaching thousand of rows.

thanks in advance to everyone who will respond.

Regards,
Bogdan

PS: this is my first time here, i hope i posted corectly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 03:43:32

[code]DECLARE @Sample TABLE
(
name CHAR(2),
dsc int
)

INSERT @Sample
SELECT 'a1', 1 UNION ALL
SELECT 'a1', 2 UNION ALL
SELECT 'a1', 3 UNION ALL
SELECT 'b1', 1 UNION ALL
SELECT 'b1', 2 UNION ALL
SELECT 'b1', 3 UNION ALL
SELECT 'b1', 6 UNION ALL
SELECT 'b1', 7 UNION ALL
SELECT 'c1', 1 UNION ALL
SELECT 'c1', 2 UNION ALL
SELECT 'd1', 1 UNION ALL
SELECT 'd1', 2 UNION ALL
SELECT 'd1', 3 UNION ALL
SELECT 'd1', 4 UNION ALL
SELECT 'd1', 5 UNION ALL
SELECT 'd1', 6

DECLARE @Source CHAR(2)

SET @Source = 'a1'

SELECT t.name
FROM (
SELECT dsc,
COUNT(*) OVER () AS c
FROM @Sample
WHERE name = @Source
) AS s
INNER JOIN (
SELECT name,
dsc,
COUNT(*) OVER (PARTITION BY name) AS c
FROM @Sample
WHERE name <> @Source
) AS t ON t.dsc = s.dsc
WHERE s.c <= t.c
GROUP BY t.name
HAVING COUNT(*) >= MAX(s.c)[/code]
EDIT: Better control

N 56°04'39.26"
E 12°55'05.63"

tempus
Starting Member

47 Posts

Posted - 2009-06-30 : 03:50:01

Dear Peso,

im gonna try that right now on my table.

thank you verry much for your quick reply.

Best regards,
Bogdan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 03:54:12

See my edited code.

N 56°04'39.26"
E 12°55'05.63"

tempus
Starting Member

47 Posts

Posted - 2009-06-30 : 04:26:07

Dear Peso,

i have created a test run that for exaclty my table:

SELECT t.MP61002
FROM (
SELECT MP61005,
COUNT(*) OVER () AS c
FROM MP610100
WHERE MP61002 = 'PF0520/R1'
) AS s
INNER JOIN (
SELECT MP61002,
MP61005,
COUNT(*) OVER (PARTITION BY MP61002) AS c
FROM MP610100
WHERE MP61002 <> 'PF0520/R1'
) AS t ON t.MP61002 = s.MP61005
WHERE s.c <= t.c
GROUP BY t.MP61002
HAVING COUNT(*) >= MAX(s.c)

where MP61002 is the column name
and MP61005 is the column desc
and MP610100 is the table name

also i have created in the system a exact copy of PF0520/R1 named PF0520/R2 with an aditional component. attached bellow is the content table:
MP61002 MP61005
PF0520/R1 01 0001 MPPEM
PF0520/R1 01 0002 MPRED
PF0520/R1 01 0003 MPWAX
PF0520/R1 01 0004 MPIRGANOX
PF0520/R2 01 0001 MPPEM
PF0520/R2 01 0002 MPRED
PF0520/R2 01 0003 MPWAX
PF0520/R2 01 0004 MPIRGANOX
PF0520/R2 01 0005 MPIOKAL

please ignore the second and third column.

the script returns in this case only the column name : "MP61002" with no result. still it should be PF0520/R2. Im sure i am doing something wrong here...

please forgive my noob'ness

tempus
Starting Member

47 Posts

Posted - 2009-06-30 : 05:58:23

Dear Peso,

thank you verry much. i have retested and it's working. thank you verry much for your help. i really apreciate it!

Best regards,
Bogdan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources