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 missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>

Alternative to Full Text Search ?

I'm aiming to provide a Ranked multiple keyword search for a varchar(3175) column on my database but Full Text Search is not an option and there could be as many as 15,000 rows in a result list.

I see that I have two choices.

1. Do everything on the Database server.
Get all results that match all submitted keywords and then count how many times each keyword is found for each record. Total each score for each record.

2. Do all of this nasty processor intenstive work on the web server
Get all results that match all submitted keywords from the database and place in a DataTable. For each record in the DataTable perform some C# based match and count loop. Place result of each loop in extra 'Result' column of DataTable and the sort DataTable according to score.

I can confidently code up option 2 however option 1 seems like a real headache in terms of the necessary SQL (my SQL is not that good). Also I think with option two I can use Caching to save the reordered datatable primary key with cache parameters based on the submitted keywords so the search should eventually get quite fast.

Does this all sound like nonsense ?
Has anyone tried to provide a Ranked multiple keyword search without using a Full Text Indexing ?

Thanks.Full Text Indexing would obviously be the right tool for this job.

What comes to mind for the next best solution would be to write a UDF which will take a keyword and a string and return the number of occurrences of the keyword in the string. Then you could sort by the number of occurrences.

The resulting query would look like this:

SELECT
 Description,
 OccurrenceCount
FROM
 (SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
WHERE
 OccurrenceCount > 0
ORDER BY
 OccurrenceCount DESC,
 Description

And the UDF would look like this:

CREATE FUNCTION dbo.fnOccurrenceCount (@.SearchNeedle varchar(8000), @.SearchHaystack varchar(8000))
RETURNS integer AS
BEGIN
DECLARE @.OccurrenceCount int
DECLARE @.FoundPosition int
DECLARE @.SearchHaystackPart varchar(1000)
DECLARE @.SearchNeedleLength int
SET @.OccurrenceCount = 0
SET @.FoundPosition = 0
SET @.SearchHaystackPart = @.SearchHaystack
SET @.SearchNeedleLength = DATALENGTH(@.SearchNeedle)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
WHILE @.FoundPosition > 0
BEGIN
SET @.OccurrenceCount = @.OccurrenceCount + 1
SET @.SearchHaystackPart = SUBSTRING(@.SearchHaystackPart,@.FoundPosition+@.SearchNeedleLength,8000)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
END
RETURN @.OccurrenceCount
END

Terri|||[sorry if this appears as a double post my last reply must have timed out]

Thanks Terri - that works really well ! - without trying to sound too dramtic - your're a life saver !

As I said in my first post my C# is OK but my T-SQL is not great. Can you recommend any books or article links I can read which could help me to work out solutions like this for myself ?

Thanks again,

TheDr|||Cool, I'm glad you were able to make use of that method!

Personally I have found that participating in forums such as this and those on aspadvice.com to be the best way to increase my knowledge. Reading other people's problems and then trying out different ways to solve them has helped me immensely -- especially when others recommend methods I hadn't even thought of. Following links that the experts post on the forums also helps.

For reading material, I would have to recommend Ken Henderson's The Guru's Guide to Transact-SQL as a must-read.

Terri|||The only question I have is from the following line of your Proc what does the 'A' represent ? If I try any run the Proc without it I'm given an error. Is it an alias for the parenthesised result ?

<snip>
(SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
</snip
Thanks for your book suggestion. I'll look out for that one.
Cheers.|||Yes, that's exactly what it is; an alias for the derived table. A lazy alias at that. When using derived tables a "correlation name alias" (as they call it) is required.

Terri