I've got two primary keys in a table:
Constraint(QueryId, 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 ?
ThxI've tried this, but it doesn't work.
CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO
fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.
Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.
CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO
fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.
Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.
CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO
fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.
Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||See this thread:
http://groups.google.ch/group/comp...62403e78dd78646
Simon|||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.constraintname)
CONSTRAINT is a reserved word and therefore not a good choice for a
table name.
--
David Portas
SQL Server MVP
--|||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|||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 ConstraintMessages :) I wrote
Constraint as it's quicker to type!
Thx
No comments:
Post a Comment