CODEWITHSUNDEEP
sql
skyline26
skyline26

Reputation: 2044

join with MULTIPLE rows for fulltext search

please read carefully because my english is not good, and the question has not an easy answer!

I have a simple structure like this:

table nodes
------------------------
nodeId | name       
     1 | Mazda Miata 2.0
     2 | Red Cars        
     3 | Mazda Cars 
     4 | Sport cars

table associations
------------------------
nodeId | hasNodeId
     1 | 2
     1 | 3
     1 | 4
     3 | 4
     3 | 1

and I want to select any row of the first table joining (in the same row) all the associated rows, according to what is specified by the table "associations"

the problem is that joining one node with one single node gives me the fulltext relevance of a SINGLE associated node: what I want is the relevance of ALL associated NODES

thank you

edit, the join result should be as you imagine like this, using the fulltext search:

nodeId | name                   | joinedName    |     fulltextRelev
     1 | Mazda Miata 2.0        | Red Cars      |     4.2
     1 | Mazda Miata 2.0        | Mazda Cars    |     2.3
     1 | Mazda Miata 2.0        | Sport Cars    |     3.2

the previous one is an abstract table, what i really want is to get unique/distinct nodeId, with the sum of the fulltextRelevance of the previous table... like this:

nodeId | name                   |     fulltextRelevSUM
     1 | Mazda Miata 2.0        |     9.7

9.7 = 4.2 + 2.3 + 3.2

Upvotes: 0

Views: 123

Answers (2)

Chris
Chris

Reputation: 387

    SELECT 
    n.nodeID,
    n.name,
    n2.name as joinedName,
    MATCH(n.name) AGAINST(n2.name) AS fulltextRelev
    FROM NODES n
    LEFT OUTER JOIN ASSOCIATIONS a
    ON n.nodeID = a.nodeID
    LEFT OUTER JOIN nodes n2
    ON n2.NODE_ID = a.hasNodeID
    WHERE n.nodeID = 1

I dont fully understand your text relevance part. can you explain it further? The above query will get you the desired first 4 rows, the query on the second part should be easy

Upvotes: 0

fancyPants
fancyPants

Reputation: 51938

You just have to group by your nodeId like this:

SELECT
nodeId, name, SUM(fulltextRelevSUM) AS fulltextRelevSum
FROM
/*your already done work here*/
GROUP BY nodeId

Upvotes: 1

Related Questions