sql - Why I can not double join query in excel

Question

I am making Excel so that I can use it like a DB. (When I press Select button, My Select statement is executed.)

However, an error occurs when I use a double join statement. Please review my code and tell me about my problem.

---------------- My Visual Basic code -----------------------------------

Sub mySelect()
    Dim myQuery As String
     
    Dim conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    
    Dim DBPath As String, sconnect As String
    
    'Delete all previously searched
    ActiveSheet.Range("B9").CurrentRegion.ClearContents
    
    DBPath = ThisWorkbook.FullName
     
    'HDR=Yes --> First row of data sheet is used as field name
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
     
    conn.Open sconnect
    myQuery = Cells(3, 2).Value
    mrs.Open myQuery, conn
    
    'print to screen
    ActiveSheet.Range("B9").CopyFromRecordset mrs
    
    'close DB recordset
    mrs.Close
    'close DB Connection
    conn.Close
End Sub

------------------ my Query ---------------------------------

SELECT req.IssueNo, req.RequestMemo FROM [IssueBasicInfo$] info

Left Join [IssueRequest$] req ON info.IssueNo = req.IssueNo

Left Join [IssueFile$] file On info.IssueNo = file.IssueNo

------------------ DB Structure -----------------------------

IssueBasicInfo

IssueFile

IssueRequest

Answer 1

This query will produce duplicate rows for req, and this is the problem. You're right that if you remove second join everything is OK. Try to change sql select to:

SELECT DISTINCT req.IssueNo, req.RequestMemo FROM [IssueBasicInfo$] info

Left Join [IssueRequest$] req ON info.IssueNo = req.IssueNo

Left Join [IssueFile$] file On info.IssueNo = file.IssueNo