request issue

[Sam]

Hi,
I've got two primary keys in a table:

Constraint(Quer yId, ConstraintName)

In a stored procedure I select {QueryId, ConstraintName} couples that
match some criteria, and what I want to do is specifying in my a SELECT
statement that I want all of the {QueryId, ConstraintName} that are not
in my stored procedure result. With only one field, it would be easy :

Select * from Constraint where QueryId not in (Select QueryId from
OtherTable)

My explanations are not great but I think it's enough to understand
what I want.

Select * from Constraint where QueryId and ConstraintName not in
(select QueryId ,ConstraintName from OtherTable)
--> of course not correct, but then how can I do that ?

Thx

[Sam]

Re: request issue

I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetCon straintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessa ges CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrp hanedMessages)
GO

fn_Admin_GetOrp hanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...

[Sam]

Re: request issue

I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetCon straintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessa ges CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrp hanedMessages)
GO

fn_Admin_GetOrp hanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...

[Sam]

Re: request issue

I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetCon straintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessa ges CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrp hanedMessages)
GO

fn_Admin_GetOrp hanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...

[Simon Hayes]

Re: request issue

See this thread:

http://groups.google.ch/group/comp.databases.ms-sqlserver/browse_frm/thread/db68c0aad7cc2a7f/c62403e78dd78646?hl=en#c62403e78dd78646

Simon

[David Portas]

Re: request issue

Use NOT EXISTS rather than NOT IN:

SELECT *
FROM [Constraint] AS T
WHERE NOT EXISTS
(SELECT *
FROM OtherTable
WHERE queryid = T.queryid
AND constraintname = T.constraintnam e)

CONSTRAINT is a reserved word and therefore not a good choice for a
table name.

--
David Portas
SQL Server MVP
--

[Stu]

Re: request issue

Try using a LEFT JOIN:

SELECT a.columnList --don't use *, explicitly name your columns
FROM TableA a LEFT JOIN TableB b ON a.Col1 =b.Col1 AND a.Col2 =b.Col2
WHERE b.Col1 IS NULL

HTH,
Stu

[Sam]

Re: request issue

I've actually solved this problem yesterday. I've done it the way David
suggested, using NOT EXISTS and it works just fine.
David, actually my table is called ConstraintMessa ges :) I wrote
Constraint as it's quicker to type!

Thx