Using subquery in a join statement in ADO SQL Excel VBA

pcanino · Nov 6, 2015

Hi everyone - if anyone can figure out what is wrong with my sqlstatement here because I'm really out of ideas on how to resolve this, i have scoured the net for days already.

Here's the query:
SELECT Sup.[SAP ID] FROM [Supply$] AS ^{LEFT JOIN [Demand$] AS [Dem] ON (Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]))

I'm executing this in Excel and I get this error:

"[Microsoft][ODBC Excel Driver] Syntax error in query expression 'Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]'."}

pcanino · Nov 6, 2015

No the sql you gave me did not work.

That's if Sup.[gcp status] IS NULL then I convert it to blank because we treat NULL values as blank, that's why I put that if statement. I'm really out of ideas, anyway, we can just focus on the first sql statement I gave, that was really where my problem is.

Kyle123 · Nov 6, 2015

Please post the code you are using to test my first sql query

P.S use code tags as well - it makes things much easier to read

Try it with the nast parentheses in the correct place

Rich (BB code):

SELECT Sup.[sap id] 
FROM   (([supply$] AS ^{LEFT JOIN [demand$] AS [Dem] 
              ON sup.[career level] = Dem.[career level] )
       LEFT JOIN [matrix$] AS [M] 
              ON M.[range level] = dem.[career level] )
WHERE  ( dem.[career level] IS NULL 
          OR M.[range_level] IS NOT NULL )}

pcanino · Nov 9, 2015

I tried your code, it did work but not exactly the result I've expected. I'm really out of idea here already. I think the subquery in the join clause does not really work after all.

Kyle123 · Nov 9, 2015

I don't really understand why you don't just put all your criteria in the where clause and forget about the correlated query

pcanino · Nov 9, 2015

I see, sorry for the confusion, I interchanged the tables. The query should be like this:

Code:

SELECT Dem.*             FROM ([Demand$] AS [Dem] 
            LEFT JOIN [Supply$] AS [Sup]
              ON Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]))

The purpose of this query is to get all the demand values without any criteria then get all the supply values that will match the criteria only. So the possible values would be that there maybe demand values without any supply values. If I put the criteria in the "where clause" then it will already give me a different output.

Kyle123 · Nov 9, 2015

Not if you check if Sup is null or has a value in each criteria. That will give you both options then.

Kyle123 · Nov 9, 2015

I *think* you want something like:

SELECT [sap id],
 [personnel name],
 Sup.[career level],
 [capability],
 [skill],
 [final proficiency],
 [skill type],
 [current ig],
 [pm],
 Iif([sap id] IS NOT NULL
 AND [onsite country] = 'USA', Datediff('m', [last repatriation date], Now()), '') AS [Last Repatriation],
 Iif([sap id] IS NOT NULL
 AND [onsite country] = 'USA', Datediff('m', Sup.[visa start date], Now()), '') AS [Months Aging],
 Iif([sap id] IS NOT NULL, Iif([onsite country] = 'USA', Iif([visa type] = 'H-1B'
 OR ( ( [visa type] = 'L-1B'
 OR [visa type] = 'L-1A' )
 AND Datediff('m', Sup.[visa start date], Now()) >= 6
 AND Datediff('m', [last repatriation date], Now()) >= 6 ), 'Yes', 'No'), 'Not Applicable; non-US demand'), '') AS [Visa Aging],
 [onsite country],
 [rrd id],
 [ready to hard lock],
 [account group],
 [client],
 [project],
 Dem.[start date],
 Dem.[end date],
 [primary skill],
 Dem.[career level]
 FROM [demand$] AS [Dem]
 LEFT JOIN [supply$] AS ^{

 ON ( Sup.[skill] = Dem.[primary skill] )

 WHERE ( Sup.[skill type] IS NULL

 OR Sup.[skill type] = 'PRIMARY SKILL ASSESSMENT' )

 AND ( Sup.[project tenure] IS NULL

 OR Sup.[project tenure] = '>=12 months' )

 AND Sup.[gcp status] IS NULL

 AND Sup.[pip status] IS NULL

 AND ( Sup.[rating - eligibility] IS NULL

 OR Sup.[rating - eligibility] = 'Yes' )

 AND ( Sup.[accenture tenure] IS NULL

 OR Sup.[accenture tenure] = 'Yes' )

 AND ( Sup.[career level] IS NULL

 OR Sup.[career level] IN (SELECT [range level 1]

 FROM [matrix$] AS [M]

 WHERE M.[range level] = Dem.[career level]) )

 AND Dem.[career level] = '09-Team Lead'

 ORDER BY [sap id] DESC

}

pcanino · Nov 9, 2015

No this isn't it, if the WHERE Clause is not met then there won't be anything displayed. There's really a big difference between putting the criteria in the WHERE Clause and on the JOIN Clause. For example if I want to make all the Demands display and wants to LEFT JOIN it to the Supply and put a condition in the LEFT JOIN Clause then if that condition is not met the Demands won't be affected by it. If you put that condition in the WHERE Clause then the Demands will be affected.

Kyle123 · Nov 9, 2015

No they won't it's a left join; that's why there's a null check in each where criteria. The only mandatory where criteria is:

Rich (BB code):

Dem.[career level] = '09-Team Lead'

pcanino · Nov 9, 2015

Ok I'll try this, but performance wise does it not slow the query time?

Using subquery in a join statement in ADO SQL Excel VBA

pcanino

New Member

pcanino

New Member

Excel Facts

Kyle123

Well-known Member

pcanino

New Member

Kyle123

Well-known Member

pcanino

New Member

Kyle123

Well-known Member

Kyle123

Well-known Member

pcanino

New Member

Kyle123

Well-known Member

pcanino

New Member

Similar threads

Forum statistics

Share this page

Using subquery in a join statement in ADO SQL Excel VBA

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock