SQL Query for 3 table with condition

Question

I have got a 3 table

DiscountHeader Table

 HeaderID    HeaderName
 ------------------------
 1            DiscountA

DiscountLine table

 LineID    HeaderID    Min    Max   DiscountPercentage
 -----------------------------------------------
 1         1           0      1000       1% 
 2         1           1001   2000       2%

and Branch_Totals

 CompanyID   HeaderID    Sales_Total      Discount
 ------------------------------------------------------
 B1          1              900              1%
 T2          1             1500              2%

And the Company Table

 CompanyID    HeaderID
 ------------------------
 B1            1
 T2            1

Basically, I would like to give a discount to each company according to their sell figure.

Each company has a Discount HeaderID and each Header has lines. Shows how much discount they will get. More sale, more discount.

for example, CompanyId 1, sold only 900, and they only entitled 1% because it is less than 1000. But companyID 2 sold 1500 and they are entitled 2%

So far i did this

 SELECT *
 CASE 
      WHEN B.Sales_Total >V.Min THEN 1
 END AS R
 FROM Branch_Totals B
 JOIN Company H ON H.CompanyID = B.CompanyID 
 JOIN DiscountLine V ON H.VDHeaderID = V.HeaderID

But when i said WHERE R not null. IT doesn't work.. Basically "AS R" not acceptable. I am not sure how to combine all these tables with a single query.

What do you mean with "not acceptable"? What is wrong with your solution? — , Feb 25 '19 at 14:02
The column alias is not available in the WHERE clause. Use a derived table. — jarlh, Feb 25 '19 at 14:03
What's the question? By the way, missing comma after select * — Janine Rawnsley, Feb 25 '19 at 14:03
https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause try to use `R IS NOT NULL` like here — demo, Feb 25 '19 at 14:04
Question is : Can anyone help me to get me the list of comapany with their disocunted rate. — AliAzra, Feb 25 '19 at 14:06
Sorry actual error is " Incorrect syntax near the keyword 'not'." As Jarlh said I can't use Column alias in Where clause — AliAzra, Feb 25 '19 at 14:12

score 2 · Accepted Answer · answered Feb 25 '19 at 14:12

You can use outer apply for this:

SELECT B.*, H.*, v.discount
FROM Branch_Totals B JOIn
     Company H
     ON H.CompanyID = B.CompanyID OUTER APPLY
     (SELECT TOP (1) v.*
      FROM DiscountLine Vv
      WHERE H.VDHeaderID = V.HeaderID AND
            B.Sales_Total >= V.Min
      ORDER BY V.MIN DESC
     ) v;

I notice that you have min and max columns, so you can also just use join:

SELECT *
FROM Branch_Totals B JOIN
     Company H 
     ON H.CompanyID = B.CompanyID JOIN
     DiscountLine V
     ON H.VDHeaderID = V.HeaderID AND
        B.Sales_Total BETWEEN v.MIN and v.MAX

SQL Query for 3 table with condition

1 Answers1