How to apply Containstable for two join tables?

Question

product table

pid modelnumber
1      a
2      b
3      c

ProductTransation

pid  name description...
1   ball   ball
2   bat     cricket bat

i create fullText for Modelnumber in product table.

Same for name & Description in productTransaction table.

Now i want to join this table if i search through modelnumber or name

result should be

pid  name  modelnumber 
1     ball  a

Answer 1

I currently do something similar to the following:

SELECT
        PD.pid,
        PD.name,
        PD.modelnumber
    FROM (
        SELECT
                (
                    COALESCE(ctP.[RANK], -1)
                    +
                    COALESCE(ctPT.[RANK], -1)
                    ) AS [RANK],
                P.pid,
                PT.name,
                P.modelnumber
            FROM Product AS P
            INNER JOIN ProductTranslation AS PT
                ON PT.pid = P.pid
            OUTER APPLY (
                SELECT TOP 1
                            ctP.[KEY],
                            ctP.[RANK]
                    FROM CONTAINSTABLE(
                        Product,
                        *,
                        @Query
                        ) AS ctP
                    WHERE ctP.[KEY] = P.pid
                )
            OUTER APPLY (
                SELECT TOP 1
                            ctPT.[KEY],
                            ctPT.[RANK]
                    FROM CONTAINSTABLE(
                        ProductTranslation,
                        *,
                        @Query
                        ) AS ctPT
                    WHERE ctPT.[KEY] = P.pid
                )
            WHERE
                (
                    ctP.[KEY] IS NOT NULL
                    OR
                    ctPT.[KEY] IS NOT NULL
                    )
        ) AS PD
    ORDER BY PD.[RANK] DESC