October 19, 2005 at 5:47 pm
Hi
I am having difficulty with combining free text tables. Any help is greatly appreciated.
Table1 consists details of goods:
ID PK
Location varchar
Table2 contains extra details of table1 (one to one relation)
ID PK (FK of Table1)
Specs varchar
When the user searches, I want to search both tables. If I query one after another and then union it works fine but now I might have duplicate data.
Select * from table1
inner join ContainsTable(table1, *, 'searchText') as S
on table1.ID = s.
Union
Select * from table1
inner join Table2 on Table1.ID = Table2.ID
inner join CotainsTable(table2, *, 'searchText') AS SS
on table1.ID = ss.
Also tried using left join, but if the first table did not have any results, it will cause problems.
Thanks.
October 21, 2005 at 5:46 am
All you should need is:
Select t1.location, t2.specs
From dbo.table1 t1
Inner Join dbo.table2 t2
On t1.id = t2.id
Or this:
Select t1.location, t2.specs
From dbo.table1 t1
Left Outer Join dbo.table2 t2
On t1.id = t2.id
The first query will only return data should matching records exist in both tables. The second query will return matching records and parent records with a null for missing child records.
October 21, 2005 at 8:51 am
You cannot union as your two queries return different number of expressions. Also if you want the rank as well then you will also get 'duplicates'.
You need to decide what to output, e.g
The following will return the unique table1 entries where a match is found in either table
Select t1.*
from table1 t1
inner join ContainsTable(table1, *, 'searchText') as S
on t1.ID = s.
Union
Select t1.*
from table1 t1
inner join Table2 t2 on t1.ID = t2.ID
inner join CotainsTable(table2, *, 'searchText') AS SS
on t1.ID = ss.
and this will return both table1 and table2 where a match is found in either table
(change inner join to left outer if relationship (table1, table2) is not 1-to-1)
select *
from table1 t1
inner join
(Select S.
from ContainsTable(table1, *, 'searchText') as S
Union
Select SS.
from ContainsTable(table2, *, 'searchText') AS SS) k
on k. = t1.[ID]
inner join table2 t2
on t2.[ID] = t1.[ID]
this may also work
select *
from table1 t1
inner join table2 t2
on t2.[ID] = t1.[ID]
where Contains(t1.*, 'searchText')
or Contains(t2.*, 'searchText')
Far away is close at hand in the images of elsewhere.
Anon.
October 24, 2005 at 7:51 pm
Hi David,
Thanks to your reply. Your answer clarified my doubts (if I wanted rank, then there will be duplicates).
All your queries worked without the ranking.
Once again thanks for your time. Have a great day.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply