Constant Column and Stock or Service Discount Setup

I didn’t update my blog for quite long time. Today, let me share some knowledge on T-SQL Programming. Last time, I posted constant table. This time, I wanna post constant fields and joining 2 tables without having any relationship.  Interesting, right? Sometimes, our customer request to generate setup data for their promotion and discount. And that time, both their services or stocks table and promotion or discount table have no relationship between them. So how we generate those data as customer requested? Let us assume stock table, STOCK, has the following format:

StockCode | Description | Price
A001 | Book | 10
A002 | Pen | 5
A003 | Pencil | 3

And the discount table, DISCOUNT, has the following format:

DiscountCode | DiscountPercentage
D010 | 10
D020 | 20
D025 | 25

Customer wants the following data:

StockCode | Price | DiscountCode | Percentage
A001 | 10 | D010 | 10
A001 | 10 | D020 | 20
A001 | 10 | D025 | 25
A002 | 5 | D010 | 10
A002 | 5 | D020 | 20
A002 | 5 | D025 | 25
A003 | 3 | D010 | 10
A004 | 3 | D020 | 20
A005 | 3 | D025 | 25

Do you notice that the data is duplicating based on DiscountCode and StockCode? Where we seen this kind of duplication? We always facing duplicate error when we join two tables wrongly, right? So now you’ll get some idea how to generate that customer request. If you still have no idea how to generate, see the following how to get those data.

At first I add constant column in each tables in query as follow:

SELECT JoinSr = 1, StockCode, Price FROM STOCK

SELECT JoinSr = 1, DiscountCode, Percentage = DiscountPercentage FROM DISCOUNT

Do you notice the column name JoinSr? That’s the constant field which will show you always one in the query result. After doing this, I’ll use our normal join statement to get the require result as follow:

SELECT S.StockCode, S.Price, D.DiscountCode, D.Percentage FROM
(SELECT JoinSr = 1, StockCode, Price FROM STOCK) S
JOIN
(SELECT JoinSr = 1, DiscountCode, Percentage = DiscountPercentage FROM DISCOUNT) D
ON S.JoinSr = D.JoinSr

Now do you notice that the error we ever facing when we join two tables is helping us to get those stock discount mapping setup.