Paradox DB SQL Multiple JOINS

Question

I'm working on a legacy VB6 project and I need to make a JOIN call like this:

SELECT C.Cnum, C.RealDate, M.Name, R.Price, R.Qnt, R.RealPrice, R.QntP, R.QntR, M.Name
FROM "CHECK" C 
LEFT JOIN "RCHECK" R ON C.Cnum = R.Cnum 
LEFT JOIN "PCHECK" P ON C.Cnum = P.Cnum 
LEFT JOIN "MONEY" M ON P.Curency = M.Sifr 
LEFT JOIN "MENU" MN ON R.Sifr = MN.Sifr 
WHERE C.Cnum > 0 ORDER BY C.Cnum

I use "Driver={Microsoft Paradox Driver (*.db )};DriverID=538" as a part of connection string but it seems it doesn't support more than one join! Which is weird.

Any ideas how to solve/workaround it?

And yes, when I run this query in Borland Database Desktop, it works fine.

Update 1:

My VB Code:

Dim Conn As New ADODB.Connection
Dim sConnStr As String
Dim sQuery As String

sConnStr = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;CollatingSequence=ASCII;DBQ=C:\DBTOFTP\BUFF;DefaultDir=C:\DBTOFTP\BUFF;PWD=SOMEPASS;"

sQuery = "SELECT C.Cnum, C.RealDate, M.Name, R.Price, R.Qnt, R.RealPrice, R.QntP, R.QntR, M.Name " & _
    "FROM ""CHECK"" C " & _
    "LEFT JOIN ""RCHECK"" R ON C.Cnum = R.Cnum " & _
    "LEFT JOIN ""PCHECK"" P ON C.Cnum = P.Cnum " & _
    "LEFT JOIN ""MONEY"" M ON P.Curency = M.Sifr " & _
    "LEFT JOIN ""MENU"" MN ON R.Sifr = MN.Sifr " & _
    "WHERE C.Cnum > 0 " & _
    "ORDER BY C.Cnum"

Conn.ConnectionString = sConnStr
Conn.Open

Have verified that the query runs with a single `LEFT JOIN` but then fails for 2 or more? — Tim Biegeleisen, Feb 10 '16 at 02:42
Can you also show us the relevant VB code where you execute this query? — Tim Biegeleisen, Feb 10 '16 at 02:50

score 5 · Accepted Answer · answered Feb 10 '16 at 12:31

5

Some old drivers often requires that multiple JOINs must be enclosed in parentheses.

Try something like this:

FROM 
    (
        "CHECK" C
        INNER JOIN 
        "RCHECK" R
            ON C.Cnum = R.Cnum 
    )
    INNER JOIN 
    "PCHECK" P
        ON P.Cnum = C.Cnum

answered Feb 10 '16 at 12:31

Paco q

74
5

Like math operations, just more parentheses for each operation. Microsoft Access creates his "joins" with this "parentheses hell", you can use it to create your queries – Paco q Feb 10 '16 at 13:28
Could you please show an example for this 4 joins? I tried to add 3rd JOIN with parentheses and it showed me an error... – Serhii Matrunchyk Feb 10 '16 at 13:31
1

`SELECT TA.fieldA, TB.fieldB, TC.fieldC, TD.fieldD FROM ((TA INNER JOIN TB ON TA.id = TB.aid) INNER JOIN TC ON TB.id = TC.bId) INNER JOIN TD ON TC.id = TD.cId;` – Paco q Feb 10 '16 at 18:06

Paradox DB SQL Multiple JOINS

1 Answers1