Click here to Skip to main content
14,357,784 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have 3 tables Product, DailyProduct and Location
Table product is master table contains column [ProductId, Name, Code, LocationId]
table DailyProduct contains column [DailyProductId, ProductName, ProductCode,
CreatedOn] and Table Location contains column [LocationId, LocationCode, LocationName]

I have to show data of dbo.DailyProduct Behalf of case bases
If DailyProduct.Created> '2018-01-01' Then apply join with ProductName else join with ProductCode.

Its not working Thanks in advance

What I have tried:

select DP.ProductName, DP.ProductCode FROM [dbo].[DailyProduct] DP
CASE WHEN DP.CreatedOn <= '2018-01-01' THEN  
LEFT JOIN Product P ON DP.ProductName = P.Name
ELSE LEFT JOIN ProductP ON DP.ProductCode = P.Code 
Updated 13-Jul-19 5:10am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Without knowing more about the schemas and data, there could be various ways to achieve what you want

One way (depending on what you really need) would just be to do 2 LEFT joins with compounded linking between the tables
SELECT Distinct dp.ProductName, dp.ProductCode
FROM   DailyProduct  dp
LEFT JOIN Product  p1 ON dp.CreatedOn <= '2018-01-01' AND dp.ProductName = p1.Name
LEFT JOIN ProductP p2 ON dp.CreatedOn >  '2018-01-01' AND dp.ProductCode = p2.Code
Maciej Los 13-Jul-19 16:02pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100