IF Clause usage in Select Query SQL Server

Question

I want to use IF statement in SELECT Query. Here is the sample for that. I have 3 table which keeps customer info, and models info, and stocks. I want to offer best fit model to each individual customer. So in my first try if customer income less then 5000 i want to offer cheap models that exist in that store, else any model in that store. But i couldnt use IF clouse in SELECT WHERE section.

SELECT  TOP 1 @vmodel  = stocks.model , @vsloc = stocks.sloc , @vyear = models.year , @vcost = models.cost <br>FROM bistore.dbo.stocks 
JOIN bistore.dbo.models
ON stocks.model = models.model 
WHERE stocks.sloc<>'Main'
AND stocks.stock > 0 
AND models.mat IN (
IF (@vincome<5000) SELECT DISTINCT mat from models where mat<>'Carbon' 
ELSE SELECT DISTINCT mat from models 
)   /* If income is less than 5000 than chose cheap models , else whatever ) */

** UPDATE **
When a customer login to my portal i want to offer him/her best model fits to him. I will check his income, store stock close to him and offer him in banner. Here is my samples from tables.

CustomerInfo

ModelInfo

DECLARE @vcname VARCHAR(50)
DECLARE @vckids INT
DECLARE @vcleng INT
DECLARE @vcweig INT
DECLARE @vcincome INT
DECLARE @vyear INT
DECLARE @vcost INT

SELECT TOP 1 @vcname = nmsnm , @vckids = kids , @vcleng = leng, @vcweig = weig , @vcincome = income FROM customers ORDER BY id DESC

DECLARE @vmodel VARCHAR(50)
DECLARE @vsloc VARCHAR(50)
SELECT TOP 1 @vmodel = stocks.model , @vsloc = stocks.sloc , @vyear = models.year , @vcost = models.cost FROM bistore.dbo.stocks
JOIN bistore.dbo.models
ON stocks.model = models.model
WHERE stocks.sloc<>'Main'

AND stocks.stock > 0
AND models.mat IN (
IF (@vincome<5000) BEGIN SELECT DISTINCT mat from models where mat<>'Carbon' END
ELSE BEGIN SELECT DISTINCT mat from models END
) /* If income is less than 5000 than chose cheap models , else whatever ) /
AND models.frames IN (SELECT frames from models where frames<48) / chose model fit to length /
AND models.type <> 'Kids Bike' / if have kids , chose whatever (not bigger then length) */
ORDER BY NEWID()

can you give us some sample data and expected result

GuidoG
– GuidoG

2017-09-07 10:45:58 +00:00
Commented Sep 7, 2017 at 10:45 — GuidoG
– GuidoG, Commented Sep 7, 2017 at 10:45

Tim Schmelter · Accepted Answer · 2017-09-07 10:55:48Z

1

You don't need IF or CASE, just an additional AND and a reversed @vincome check:

SELECT TOP 1 
       @vmodel = stocks.model , 
       @vsloc =  stocks.sloc , 
       @vyear =  models.year , 
       @vcost = models.cost <br> 
FROM   bistore.dbo.stocks 
JOIN   bistore.dbo.models 
ON     stocks.model = models.model 
WHERE  stocks.sloc <>'Main' 
AND    stocks.stock > 0 
AND ( @vincome >= 5000 OR models.mat <> 'Carbon' )

You see that you don't even need a subquery.

answered Sep 7, 2017 at 10:55

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mehmet Kaplan · Accepted Answer · 2017-09-07 10:55:40Z

In MySQL it is the CASE statement of which details are here.

A possible usage (please refer your objects for correct syntax):

SELECT  TOP 1 @vmodel  = stocks.model , @vsloc = stocks.sloc , @vyear = models.year , @vcost = models.cost <br>FROM bistore.dbo.stocks 
JOIN bistore.dbo.models
ON stocks.model = models.model 
WHERE stocks.sloc<>'Main'
AND stocks.stock > 0 
AND models.mat IN (
    CASE sign(@vincome - 5000)
       WHEN -1 then (SELECT DISTINCT mat from models where mat<>'Carbon')
       ELSE '(SELECT DISTINCT mat from models ) 
    END
    )   /* If income is less than 5000 than chose cheap models , else whatever ) */

PS: Sign is a trick I use to mimic greater / equal / smaller control for better comprehension of CASE syntax.

Collectives™ on Stack Overflow

IF Clause usage in Select Query SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related