CODEWITHSUNDEEP
sqlexcelvbams-access
Dom
Dom

Reputation: 83

SQL Syntax Error in Join Operation with Subquery in FROM clause

I've been bashing my head against this one and I'm sure it's a super simple answer. in Excel 2016 VBA I have the below SQL string, but I'm getting a Syntax Error on the Join Operation.

SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
FROM((
    SELECT *
        FROM [Catalogue Info] as c
        LEFT JOIN [Product Information] as p on c.code = p.code
    )
    LEFT JOIN [;database=C:\mypath\db.accdb;PWD=1234].tbl as pl on c.code = pl.product_code
    )
WHERE c.Category = 'VIENNOISERIE'
AND c.Sub_Cat_1 = 'CROISSANTS'
AND c.Sub_Cat_2 = ''
AND p.active = true
ORDER BY c.Page ASC, c.Page_Position ASC;

I've tried using the second LEFT JOIN inside the subquery, but I'm struggling to reference it in the SELECT clause.

Any thoughts?

Upvotes: 1

Views: 261

Answers (3)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

This would do I think,

SELECT j.code, j.description, j.weight, j.[pack size], j.ppb, j.[CP-UK], j.[CP-EU]
FROM(
    SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU],
c.Category,c.Sub_Cat_1,c.Sub_Cat_2,p.active,c.Page,c.Page_Position
        FROM [Catalogue Info] as c
        LEFT JOIN [Product Information] as p on c.code = p.code
    )j
    LEFT JOIN [;database=C:\mypath\db.accdb;PWD=1234].tbl as pl on j.code = pl.product_code

WHERE j.Category = 'VIENNOISERIE'
AND j.Sub_Cat_1 = 'CROISSANTS'
AND j.Sub_Cat_2 = ''
AND j.active = true
ORDER BY j.Page ASC, j.Page_Position ASC;

Upvotes: 1

user2042214
user2042214

Reputation: 165

Use below code

SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
FROM((
    SELECT *
        FROM [Catalogue Info] as c
        LEFT JOIN [Product Information] as p on c.code = p.code
    ) as t
    LEFT JOIN [;database=C:\mypath\db.accdb;PWD=1234].tbl as pl on t.code = pl.product_code
    ) as t1
WHERE t1.Category = 'VIENNOISERIE'
AND t1.Sub_Cat_1 = 'CROISSANTS'
AND t1.Sub_Cat_2 = ''
AND pactive = true
ORDER BY Page ASC, Page_Position ASC;

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33474

Assuming the way you are querying external database is a correct syntax

    SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
        FROM [Catalogue Info] as c
        LEFT JOIN [Product Information] as p on c.code = p.code
    LEFT JOIN [;database=C:\mypath\db.accdb;PWD=1234].tbl as pl on c.code = pl.product_code
WHERE c.Category = 'VIENNOISERIE'
AND c.Sub_Cat_1 = 'CROISSANTS'
AND c.Sub_Cat_2 = ''
AND p.active = true
ORDER BY c.Page ASC, c.Page_Position ASC;

Also, why are you including external db (.tbl as pl) when you are not using it in the SELECT statement?

Upvotes: 1

Related Questions