Select from two different tables, depending on name?

1

Hi everyone.

Im having a small problem. Hope someone can help me :slight_smile: I would like to make an if statement, such that it uses two different tables, depending on the product name? (I have already made the below into a stored procedure, i understand that it's a stupid example with a static name, but hope you get my drift). My problem is that i don't understand, how i should do write the code, such that it understands that if the product name contains certain characters, it should look into a different table than tbSourceData?

 [select pvt.Dates
         , pvt.1stProduct
         , pvt.2ndproduct
         , JR.discountd_prices
    from 
    	(
    	select Name, Dates, Prices
    	from dbo.tbSourceData
    	) tb
    pivot ( max(Prices) for Name in (1stProduct, 2ndProduct)
    ) pvt
    left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = '1stProduct'
    where pvt.Dates > getdate()-100]
2

I tried myself with the following

 select pvt.Dates
         , pvt.1stProduct
         , pvt.2ndproduct
         , JR.discountd_prices
    from 
    	(
		if charindex('3rd', '3rdproduct') >0 
    		select Name, Dates, Prices
    		from dbo.tbSourceData1
		else 
    		select Name, Dates, Prices
    		from dbo.tbSourceData2
    	) tb
    pivot ( max(Prices) for Name in (1stProduct, 2ndProduct)
    ) pvt
    left join tbSourceData JR on JR.Dates = pvt.Dates and JR.Name = '1stProduct'
    where pvt.Dates > getdate()-100

My if statement works, if it's isolated, but i don't know how i should write it, if i want to do a pivot table, or something different? It seems it's the parenthesis and naming it "tb" which it does not like. How do i solve this?

3

You either have to use dynamic SQL or write two separate statements and just run the one that matches the IF:

IF (SELECT Name FROM dbo.tbSourceData /*WHERE ...*/) = '1stProduct'
BEGIN
    SELECT ...
    FROM tableA
    ...
END /*IF*/
ELSE
BEGIN
    SELECT ...
    FROM tableB
    ...
END /*ELSE*/
4

Thanks for the answer! :slight_smile: But if i wanted to write it as dynamic sql, i still would have to actually know how to write the code. At the moment that doesn't work very well.

		if charindex('3rd', '3rdproduct') >0 
    		select Name, Dates, Prices
    		from dbo.tbSourceData1
		else 
    		select Name, Dates, Prices
    		from dbo.tbSourceData2

Seems to work, but if i want to do something with my code, in this example make a pivot table, i would write

select * from 
    (		
    		if charindex('3rd', '3rdproduct') >0 
        		select Name, Dates, Prices
        		from dbo.tbSourceData1
    		else 
        		select Name, Dates, Prices
        		from dbo.tbSourceData2
    ) tb
    pivot (max(Prices) for name in (1stProduct, 2ndProduct)) pvt

It says im doing something wrong, and i cant figure out why?