Combine CONTAINSTABLE results from 2 tables
-
Hi
Could you SQL gurus help me out a bit.
I have one-to-many table structure and I'm trying to use CONTAISTABLE search to search through both tables and show results with rank.
Containstable search is working already but I don't know how to combine values correctly.
Sample db data:
PARENT_TABLE
-------------------------------------------
ID | Title
-------------------------------------------
1 | 'Issue1 Title'
CHILD_TABLE
-------------------------------------------
ID| PID | RespText
--------------------------------------------
1 | 1 | 'First Response to Issue1 1'
2 | 2 | 'Second Response to Issue1 2'
SQL:
SELECT DISTINCT title,
ISNULL(A.RANK, 0) + ISNULL(A.RANK, 0) AS TOTALRANK
FROM PARENT_TABLE
LEFT OUTER JOIN CONTAINSTABLE(PARENT_TABLE,(title), @searchvalue) AS A ON PARENT_TABLE.ID = A.
LEFT OUTER JOIN CONTAINSTABLE(CHILD_TABLE,respText, @searchvalue) AS B ON CHILD_TABLE.ID = B.
WHERE
A.RANK IS NOT NULL OR B.RANK IS NOT NULL
If I search for 'Issue1', resultset shows 3 rows with RANK for each row.
E.g
RESULT:
----------------------------------
Title | RANK
----------------------------------
'Issue1 Title' | 100 (not real values here)
'Issue1 Title' | 80
'Issue1 Title' | 80
What I want to get is:
RESULT:
----------------------------------
Title | RANK
----------------------------------
'Issue1 Title' | 260 (100 + 80 + 80)
So search for word 'Issue1' would return only one row and RANK would be sum of all 3 RANKS.
I really hope that someone has solved this already, I'm amazed that I actually get even this far with my very limited SQL skills.
Sorry about crappy English
- Jouni -
-
Problem solved.
SUM(CONVERT(int,(ISNULL(A.RANK, 0) + ISNULL(A.RANK, 0))) AS TOTALRANK
did the trick
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply