Click here to Skip to main content
15,921,793 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
what is the best way how to solve this situation?
Let's say you have a table Property with columns
Property ID, Property Description, Property Type, Property Type Related ID....
If Property Type is Selling then Property Type Related ID is from table for selling
If Property Type is Renting then Property Type Related ID is from table for renting

SELECT
propertyID, 
propertyDescription, 
(CASE WHEN relatedSelling.selling ID IS NOT NULL THEN relatedCOl1
     WHEN relatedRenting.selling ID IS NOT NULL THEN relatedCOl1
END) as RelCol1,
(CASE WHEN relatedSelling.selling ID IS NOT NULL THEN relatedCOl2
     WHEN relatedRenting.selling ID IS NOT NULL THEN relatedCOl2
END) as RelCol2,


..... is there a way how to get this data and not having case for each column? there is about 15 columns which I need to have in a select from those related tables(or other tables joined with these related)

FROM Property
LEFT JOIN ON selling WHERE property. propertyTypeRelatedID = selling ID and property.propertyType = 'Selling' as relatedSelling
LEFT JOIN ON renting WHERE property. propertyTypeRelatedID = renting ID and property.propertyType = 'Renting' as relatedRenting
Posted

Use union all :
SQL
select col1,...,colN from table1 left join ..sellingtable.. where ...  
union all
select col1,...,colN from table1 left join ..rentingtable.. where ...
 
Share this answer
 
Comments
Petra Cerna 4-Mar-13 8:59am    
thank you for your reply, but in this case you would have in the result columns from selling table plus columns from renting table...I need results only from one of them (depending on type)
Mehdi Gholam 4-Mar-13 9:03am    
Write 2 queries one which will get you the "selling" and the other the "renting" then union the two.
Petra Cerna 4-Mar-13 9:10am    
not sure about the union, but sth like this?
SELECT propertyType FROM Property
if propertyType = selling
then query 1
if propertyType = renting
then query 2
if propertyType IS NULL
then query 3
Mehdi Gholam 4-Mar-13 9:23am    
Write the query you want for propertytype = selling then wrie the same query with propertytype renting and union the two queries.
Petra Cerna 4-Mar-13 9:43am    
I have tried it, but it does what I wrote in the beginning - I'm getting results from both tables, I need results just from one of them...
Hi,

U need to do Dynamic Query for this....

Check the following Links...

Building Dynamic SQL In a Stored Procedure[^]
Execute Dynamic SQL commands in SQL Server[^]


Regards,
GVPrabu
 
Share this answer
 
Comments
Petra Cerna 4-Mar-13 12:18pm    
thanks, have never heard about this before, but it looks like a good solution

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900