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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply