Combine MATCH clause and INNER JOIN in SQL Server graph database with mixed model

Question

I have a some node and edge tables in SQL Server with a one to many relationship to a standard table which store users. Each edge and node table have this 1N relationship.

I would like to know how can I perform a query with match clause and a left join like this :

SELECT * FROM Node1Table n1, EdgeTable e, Node2Table n2
LEFT JOIN UserTable usr ON e.usr = usr.ID
MATCH (n1-(e)->n2)

I could write the query like this :

SELECT * FROM EdgeTable e
INNER JOIN Node1Table n1 ON e.$from_ID = n1.$node_ID
INNER JOIN Node2Table n2 ON e.$to_ID = n2.$node_ID
LEFT JOIN UserTable usr ON e.usr = usr.ID

But I don't know if n1 is from or to object.

I can't do an inner join because e.usr could be null

Thank you for your help

EDIT:

Test 1 :

SELECT * FROM
OBJ_APPLICATION n1, REL_APPLICATION_RESPONSABLE r INNER JOIN    
Management_User u on u.[UserID] = r.[CPQ], OBJ_RESPONSABLE n2
WHERE MATCH(n1-(r)->n2)

error : The identifier "r" in a MATCH clause is used with a JOIN clause or an APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Test 2 :

SELECT * FROM
OBJ_APPLICATION n1, REL_APPLICATION_RESPONSABLE r, OBJ_RESPONSABLE n2
INNER JOIN Management_User u on u.[UserID] = r.[CPQ]
WHERE MATCH(n1-(r)->n2)

error : The multi-part identifier r.CPQ could not be bound

Test 3 :

SELECT * FROM
OBJ_APPLICATION n1, REL_APPLICATION_RESPONSABLE r, OBJ_RESPONSABLE n2, Management_User u
WHERE MATCH(n1-(r)->n2)
AND u.[UserID] = r.[CPQ]

Works with an INNER JOIN but in some cases I have to make a LEFT JOIN

Never used Graph functions myself, but I would suggest that you first calculate your graph result and then join to users e.g. `SELECT * FROM (SELECT * FROM .... MATCH (n1-(e)->n2)) AS g LEFT JOIN UserTable usr ON g.usr = usr.ID`. — Alex, Jun 11 '20 at 22:15

acivic2nv · Answer 1 · 2021-03-15T19:03:20.747

Like Alex suggested, you can do a sub query. Below is a fully functioning query that I am actively working on.

SELECT
TokensConnectors.*
FROM
(
    SELECT      
        leftWord.word As LeftSide, 
        MiddleWord.word AS MiddleWord,
        DependsOn1.[DependencyType] As [DependencyType1],
        DependsOn1.SentenceId
    FROM 
        Pipeline.Words MiddleWord,
        Pipeline.Words leftWord, 
        Pipeline.DependsOn DependsOn1
    WHERE 
        MATCH(leftWord<-(DependsOn1)-MiddleWord)
        AND leftWord.Word = 'differential'
        and middleWord.word = 'diagnosis'
) AS DifferentialDiagnosis 
INNER JOIN [Pipeline].[DependsOn] AS TokensConnectors ON TokensConnectors.[SentenceId] = DifferentialDiagnosis.SentenceId

Here is an example where I inner join Sentences, ClinicalNotes and DependsOn1. Think link is done in the Where statement.

SELECT
    DependsOn1.SentenceId
FROM 
    Pipeline.Words RightWord,
    Pipeline.Words LeftWord, 
    Pipeline.DependsOn DependsOn1,
    Pipeline.Sentences,
    Pipeline.ClinicalNotes
WHERE 
    MATCH(LeftWord<-(DependsOn1)-RightWord)
    AND LeftWord.Word = 'differential'
    and RightWord.word = 'diagnosis'
    AND DependsOn1.SentenceId = Sentences.Id
    AND ClinicalNotes.Id = Sentences.ClinicalNoteId
    AND ClinicalNotes.ProcessingLogId = @processingLogIdessingLogId

Combine MATCH clause and INNER JOIN in SQL Server graph database with mixed model

1 Answers1