mattlore mattlore - 3 months ago 18
SQL Question

Select Top 1 with Switch Statement and Left join Access SQL

So I'm not sure how much I've screwed up this Access SQL query, but it's fairly complex.

Here's the scenario: There are three tables. One called "PriceMatrix", one called "PreApprovedPricing" and finally one called "Request". Approved pricing contains a list of "Approved" prices by state and "Price Matrix" contains a list of prices generated from another source and Request is the parent record that all of these point back to (Which also contains the state). I'm trying to write a query that compares the prices in each table and if the pre-approved price is less than the price in the price matrix, it replaces the value in the field, but if it's greater than, then it keeps the original price matrix price. I've tried using a switch statement to look at the state given.

Here's my query:

SELECT TOP 1 [ApprovedPrice] from [PreApprovedPricing] AS [1stMonthApproved] WHERE [ApprovedPrice] >= [PriceMatrix].[1stMonth]
ORDER BY [ApprovedPrice] ASC,
SWITCH( Request.[state] = 'CT' , [1stMonthApproved], Request.[state] = 'MA' , [1stMonthApproved], 1=1 , [PriceMatrix].[1stMonth]) AS 1stMonth,
FROM PriceMatrix LEFT JOIN Request ON PriceMatrix.RequestID = Request.RequestID;


I keep getting syntax errors but I can't seem to drill down where the issues is stemming from. So any input would be great as I am still learning SQL

Answer

You can't have multiple From clauses. Your statement

SELECT TOP 1 [ApprovedPrice] 
from [PreApprovedPricing] AS [1stMonthApproved] 
WHERE [ApprovedPrice] >= [PriceMatrix].[1stMonth]
ORDER BY [ApprovedPrice] ASC,
SWITCH( Request.[state] = 'CT' , [1stMonthApproved], Request.[state] = 'MA' , [1stMonthApproved], 1=1 ,  [PriceMatrix].[1stMonth]) AS 1stMonth,
FROM PriceMatrix 
LEFT JOIN Request ON PriceMatrix.RequestID = Request.RequestID;

Is actually:

Select Top
From
Where
Order by
From
Left Join

That will definitely get syntax errors. I assume you want a sub query in there somewhere. Maybe like this (but hard to tell for sure):

SELECT [ApprovedPrice] ,
    (Select top 1 SWITCH( Request.[state] = 'CT' , [1stMonthApproved], Request.[state] = 'MA' , [1stMonthApproved], 1=1 ,  [PriceMatrix].[1stMonth])
    from [PreApprovedPricing] AS [1stMonthApproved] 
    WHERE [ApprovedPrice] >= [PriceMatrix].[1stMonth]
    ORDER BY [ApprovedPrice] ASC,
        SWITCH( Request.[state] = 'CT' , [1stMonthApproved], Request.[state] = 'MA' , [1stMonthApproved], 1=1 ,  [PriceMatrix].[1stMonth])) AS 1stMonth
FROM PriceMatrix 
LEFT JOIN Request ON PriceMatrix.RequestID = Request.RequestID;
Comments