Find uniques on part of a multi-column PK (with where clause)

Hello all,
Follows a simplified DDL of the table where i'm working on:
CREATE TABLE Test
(
TestID bigint NOT NULL ,
SubTestID bigint NOT NULL ,
Name varchar (50),
Master bigint NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestID,
SubTestID
)
)
What I'm trying to achieve is to collect and retrieve all rows of which
Master differs from the value 2 and TestID is unique.
So far, the best I achieved was:
SELECT
counter.TestID
FROM
(
SELECT
TestID,
COUNT(TestID) AS Total
FROM
Test
WHERE Master <> 2
GROUP BY
TestID
) as counter
WHERE counter.Total = 1
However this strategy doesn't suit me since I need the TestID/SubTestID
pair to be returned. Needless to say, introducing SubTestID in the
above query will invalidate de aggregate function.
Can someone please help?Mario
Needless to say, that posting sample data + expected result will help to
bring a right solution. No offence.
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Lazy solution:
SELECT TestID, MAX(SubTestID ) AS SubTestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1
More structured solution:
SELECT t1.TestID, t1.SubTestID
FROM Test t1
INNER JOIN (
SELECT TestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1) a
ON t1.TestID = a.TestID
Jacco Schalkwijk
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Try,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
COUNT(distinct TestID) = 1
) as a
on t.TestID = a.TestID
go
AMB
"Mario F." wrote:

> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||One method:
SELECT
a.TestID,
a.SubTestID
FROM Test a
JOIN
(
SELECT
TestID
FROM Test
WHERE Master <> 2
GROUP BY
TestID
HAVING COUNT(*) = 1
) AS counter ON counter.TestID = a.TestID
Hope this helps.
Dan Guzman
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Correction,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
min(SubTestID) = max(SubTestID)
) as a
on t.TestID = a.TestID
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t.*
> from
> test as t
> inner join
> (
> SELECT
> TestID
> FROM
> Test
> WHERE
> Master <> 2
> GROUP BY
> TestID
> having
> COUNT(distinct TestID) = 1
> ) as a
> on t.TestID = a.TestID
> go
>
> AMB
> "Mario F." wrote:
>|||The Having clause. Of course. Thank you everyone.
Mario F.
*** Sent via Developersdex http://www.examnotes.net ***sql