An "EXISTS" Problem

Hey, guys,
Below are my DDL,
CREATE TABLE [dbo].[test1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
CREATE TABLE [dbo].[test2] (
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
test1 table
id A B
--
1 c 1
2 c 2
3 c 3
4 c 4
6 b
9 d
10 e
test2 table
A B
--
c 1
b 1
b 2
d 1
result table
id A B
--
2 c 2
3 c 3
4 c 4
6 b
9 d
Here is my sql to get the result table
SELECT P.id,P.A,P.B
FROM test1 P left outer join test2 R on P.A=R.A
WHERE (NOT EXISTS
(SELECT *
FROM test2 Q
WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
and R.A is not null
group by P.id,P.A,P.B
Can this SQL command be neater?
thanks a lot.
AllenHere are a couple of other methods, although 'cleaner' is a bit subjective'.
Personally, I prefer the NOT EXISTS technique over LEFT JOIN.
INSERT INTO test1 VALUES(1,'c',1)
INSERT INTO test1 VALUES(2,'c',2)
INSERT INTO test1 VALUES(3,'c',3)
INSERT INTO test1 VALUES(4,'c',4)
INSERT INTO test1 VALUES(6,'b', NULL)
INSERT INTO test1 VALUES(9,'d', NULL)
INSERT INTO test1 VALUES(10,'e', NULL)
GO
INSERT INTO test2 VALUES('c', 1)
INSERT INTO test2 VALUES('b', 1)
INSERT INTO test2 VALUES('b', 2)
INSERT INTO test2 VALUES('d', 1)
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
WHERE NOT EXISTS
(
SELECT *
FROM test2 Q
WHERE
P.A = Q.A AND P.B = Q.B
)
GROUP BY P.id, P.A, P.B
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
LEFT JOIN test2 Q ON P.A = Q.A AND P.B = Q.B
WHERE Q.A IS NULL
GROUP BY P.id, P.A, P.B
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allen" <cpchen@.cht.com.tw> wrote in message
news:u19bQ77tDHA.2408@.tk2msftngp13.phx.gbl...
> Hey, guys,
> Below are my DDL,
> CREATE TABLE [dbo].[test1] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> CREATE TABLE [dbo].[test2] (
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> test1 table
> id A B
> --
> 1 c 1
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> 10 e
> test2 table
> A B
> --
> c 1
> b 1
> b 2
> d 1
>
> result table
> id A B
> --
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> Here is my sql to get the result table
> SELECT P.id,P.A,P.B
> FROM test1 P left outer join test2 R on P.A=R.A
> WHERE (NOT EXISTS
> (SELECT *
> FROM test2 Q
> WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
> and R.A is not null
> group by P.id,P.A,P.B
>
> Can this SQL command be neater?
>
> thanks a lot.
> Allen
>

am I reading this right?

When I see this in my Maintenance Plan job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
It could mean a FF of 10, but thats not the way Im reading it.
TIA,
ChrisR
Yes That is a fillfactor of 90%
http://sqlservercode.blogspot.com/
"ChrisR" wrote:

> When I see this in my Maintenance Plan job:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
> I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
> It could mean a FF of 10, but thats not the way Im reading it.
> --
> TIA,
> ChrisR

am I reading this right?

When I see this in my Maintenance Plan job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
It could mean a FF of 10, but thats not the way Im reading it.
TIA,
ChrisRYes That is a fillfactor of 90%
http://sqlservercode.blogspot.com/
"ChrisR" wrote:

> When I see this in my Maintenance Plan job:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
> I think that means a Fill Factor of 90%, leaving 10% free. Is that correct
?
> It could mean a FF of 10, but thats not the way Im reading it.
> --
> TIA,
> ChrisR

am I reading this right?

When I see this in my Maintenance Plan job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
It could mean a FF of 10, but thats not the way Im reading it.
--
TIA,
ChrisRYes That is a fillfactor of 90%
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> When I see this in my Maintenance Plan job:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
> I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
> It could mean a FF of 10, but thats not the way Im reading it.
> --
> TIA,
> ChrisR