CODEWITHSUNDEEP
sqlsql-server
user3248713
user3248713

Reputation:

Sorting Sql Table by combining multiple column as a column

Please help me. I have a problem in sorting result. I have a product table name with the columns 

ProductID, name, price,...

and the Promotion table with columns

PromoID, startDate, EndDate, SalePrice, ProductID,.....

I want to retrieve the products by price.

I want to sort the price by asc and desc. I want to sort the price column in Product table and SalePrice column in Promotion table as a one column because I want to show promotion price to users if the product is on promotion.

Please how can I solve that problem? Is it possible to combine multiple columns as one in sorting?

Upvotes: 1

Views: 78

Answers (2)

akhil kumar
akhil kumar

Reputation: 1618

Use the query

select (what you want) 
from product 
inner join promotion on product.productid = promotion.promoid 
order by price, salesprice asc

(what you want) should not be ambiguous, i.e. use like product.productprice, promo.promoprice etc.

Upvotes: 0

Abecee
Abecee

Reputation: 2393

As you seem to want a single price column try along

SELECT
    Prod.productID,
    ISNULL(Prom.salePrice, Prod.price)
FROM Product Prod
JOIN Promotion Prom
  ON Prod.productID = Prom.productID
  AND Prom.startDate <= CONVERT (date, GETDATE())
  AND Prom.endDate >= CONVERT (date, GETDATE());

Upvotes: 4

Related Questions