CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Görkem Yazıcı
Görkem Yazıcı

Reputation: 33

Can I select the variable in where clause in MSSQL?

I want to select the variable in where clause according to query result.

Select table1.*, table2.color, table3.type
from table1
inner join table2 on table1.ID=table2.table1Id
inner join table3 on table1.ID=table3.table1Id
where table3.type = @x OR table3.type = @y

| productName | Category    | color | type |
| abc         | electronics | blue  |  x   |
| abc         | electronics | blue  |  y   |
| def         | electronics | red   |  x   |

This query can returns duplicate result because product can has two types. I want to select the variable in where clause. For example, I want to get products that have type of @y but if product's @y type is not exist I want to return @x type. I don't want first abc row in example result. Can you please help about my query?

Upvotes: 1

Views: 65

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

use row_number() over() to calculate the preferred rows you do want from table3

SELECT
      table1.*
    , table2.color
    , t3.type
FROM table1
INNER JOIN table2 ON table1.ID = table2.table1Id
INNER JOIN (
      SELECT
            table3.table1Id
          , table3.type
          , ROW_NUMBER() OVER (PARTITION BY table3.table1Id
                              ORDER BY table3.type DESC)    AS rn
      FROM table3
      ) t3 ON table1.ID = t3.table1Id
            AND t3.rn = 1

adjust the order by to suit, for example it could also include a case expression e.g.

ORDER BY case when t3.type = 'y' then 1 else 2 end, t3.type

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use a correlated query with order by:

Select table1.*, table2.color,
       (SELECT TOP 1 table3.type
        FROM Table3
        WHERE table1.ID=table3.table1Id
        ORDER BY CASE WHEN table3.type = @y THEN 1 
                      WHEN table3.type = @x THEN 2
                      ELSE 3 END)
from table1
 inner join table2 on table1.ID=table2.table1Id
 inner join table3 on table1.ID=table3.table1Id

The correlated query will return @y if exists, if not , will return x .

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Select table1.*, table2.color, table3.type from table1
inner join table2 on table1.ID=table2.table1Id inner join table3 on table1.ID=table3.table1Id where table3.type = CASE WHEN LEN(@x) > 0 THEN @x WHEN LEN(@y) > 0 THEN @y END

Upvotes: 1

Related Questions