Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.

Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.

multiple columns in "in" search

Is it possible to put a set of columns into a select statement using "in"

For example:

select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)

This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.

Any ideas?

No. SQL Server doesn't support row value constructors yet. You can use a correlated sub-query instead like:

select count(*) from table1 as b where exists (select * from table1 a

where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4)

|||

Thanks, that solves part of my problem.

What I want to be able to do is to delete using the same (similar) syntax, but from an earlier seperate post I cant prefix a table in a delete query!?!?

My select query is:

select count(*) from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

The way it works is that every person can be registered for a specific language and a specific course. They can sit am exam for a specific course and language as many times as they want, but they get a new entry in the datas1 table but with an incremented exam number from the previous entry. All other values can be the same. Now we want to be able to delete some of the older rows from the datas1 table (the earlier exam entries and keep the most recent ones up to a number specified in the NUMBER variable)

For example, a customer 1001 has sat language 1 in course 2 exam 5 times. If I set the NUMBER variable to 3 then all I want to remain in the datas1 table is entries for exam 3, 4 and 5
The sql above will work as a select but cannot get it to work when trying to do the delete .eg.

delete from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

Please help

|||

You need to use the TSQL extension for the DELETE statement. ANSI SQL doesn't allow aliasing of source table in the DELETE statement. You can modify your DELETE statement like:

delete from schema.datas1 /* note additional FROM clause here */

from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

|||

That works great. Thanks!

One more question.

Now I want to break my deletes from the datas1 table into batches of say 1000 records at a time using a loop statement in perl. What exactly would be the transact sql (I assume using top) to do this?

Again I can get this to work for the select statement, but having difficulty with the delete.

Many Thanks!

|||

In SQL Server 2005, you can also specify TOP clause for DML statements. So you can run a batch like below which will delete 1000 rows at a time:

while(1=1)

begin

delete top(1000) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

while(1=1)

begin

delete top(@.n) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

In older versions of SQL Server, you can do the same using SET ROWCOUNT like.

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

set rowcount @.n

while(1=1)

begin

delete from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

set rowcount 0