July 20, 2004 at 4:33 pm
I'm fairly experience with sql, but not experienced at all with sql server. I have a problem at work where I have 2 tables. One table I have 3 important fields: one that is a text field (field1), one that is its primary key field (field2) and one that is a primary key of a second table (field3) (but there maybe duplicates value of this in the first table). So for each row in the second table, there maybe multiple records in the first table whose value of field3 is the same as second table's primary key
Currently, what I am doing is running a
"select distinct field3 from table1 where contains(contains,....)"
statement on the text field in the first table to get all the distinct keys of the second table. Then would I do is go through all the keys in the resultset and then run on the second table
"select * from table2 where (field3=1 or field3=2 or field3=5 or field3=6.....)"
Something like that. If a lot of rows result from the first query, you can see that it will create a problem for the second., because the or query will go on for too long.
How can I do this using subquery (I never worked it before). Can I do this with one sql statement executed against the table or do I need to use a stored procedure? And what what the stored procedure look like (sorry, I never worked with stored procedures either because much of my experience have being with databases like access, foxpro and mysql)
Thank you.
July 20, 2004 at 5:58 pm
Hi FenqC, your question is slightly unclear. Can you give an example of a few records in tables A and B and what you want to return? I'm sure that we'll be able to give you a query that will do that for you - you will not need a stored procedure.
For examples of stored procedures, check out the Master database, which contains hundreds. Typically, you would use a stored procedure to perform a discrete series of actions on a server (eg inserts, updates, deletes ...). They tend to run very fast because they are optimised by SQL Server and also because network bandwidth requirements are minimised. Stored procedures can accept and return values and resultsets. A big topic.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2004 at 7:15 pm
sure in tablea, I might have
row1:
field1 - "A lot of people like to run, but I liked to hide"
field2 - 1 (primary key)
field3 - 1
row2:
field1 - "Not a lot of people like to hide, but I run"
field2 - 2
field3 - 1
row3:
field1 - "Haha, no body likes to hide"
field2 - 3
field3 - 2
in tableB
field1 - 1
FirstName - Feng
LastName - C
... (some other data)
field1 - 2
FirstName - Bob
LastName - D
...(some other data)
so, right now, I search for CONTAINS(field1, '"run" AND "hide"')
so the first and second record in table1 would fit. Now, the table is constructed in such a way that field3 in tableA corresponds to field1 in tableB (so, basically each row in tableB is mapped to multiple entries in tableA). And both first and second record of tableA are mapped to first record of tableB. I want Feng and C returned.
I want something like
select tableB.FirstName, tableB.LastName from tableB where tableB.field1 = {select tableA.field3 from tableA where CONTAINS(field1, '"RUN" and "hide"')
Thanks.
July 20, 2004 at 7:51 pm
Something like this?
select distinct b.firstname, b.lastname
from tablea a
inner join tableb b on a.field3 = b.field1
where contains(a.field1, ' "run" and "hide" ')
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2004 at 7:57 pm
thanks, what does inner join do exactly?
July 20, 2004 at 8:11 pm
My previous query is equivalent to the following:
select distinct b.firstname, b.lastname
from tablea a, tableb b
where contains(a.field1, ' "run" and "hide" ') and
(a.field3 = b.field1)
so that should help you understand. To quote BOL:
"Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables."
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2004 at 8:21 pm
but what about the fact that there might be more than 1 record mapped from first record to second?
There can be multiple rows in a where it's field3 is equal to the same value for field1 in b. Does that matter?
What I means is that if you just run
select field3 from tablea where contains(field1, '"RUN" and "HIDE"')
you are likely to have results that are the same. You won't get all distinct field3 values.
July 20, 2004 at 8:25 pm
As a result of using the 'distinct' keyword, duplicate records in the output recordset are not displayed. Remove 'distinct' from my query and the multiple records will be displayed.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 20, 2004 at 9:02 pm
thanks.
July 22, 2004 at 1:52 pm
Phil,
When I tried this on a table here, I received the following message:
"Cannot use a CONTAINS or FREETEXT predicate on table 'NAMES' because it is not full-text indexed." What does this mean? TIA
Terry
July 22, 2004 at 5:54 pm
By default, SQL Server tables/databases are not full-text indexed. As the error message indicates, you cannot use CONTAINS or FREETEXT within queries unless the underlying data is full-text indexed.
As you might imagine, indexing is a performance and space overhead, however, if you need to perform sophisticated searches, it may be necessary. Check BOL for more info.
The query above can, however, be rewritten using the CHARINDEX function, which does not rely on full-text indexing:
...
where (charindex('run', a.field1) 0 and charindex('hide', a.field1) 0)
...
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply