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
Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.
Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.
There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With