efficiency of the SQL query

Question

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 4-Mar-13 2:26am

Petra Cerna

Add a Solution

2 solutions

Solution 1

Use union all :

SQL

select col1,...,colN from table1 left join ..sellingtable.. where ...  
union all
select col1,...,colN from table1 left join ..rentingtable.. where ...

Posted 4-Mar-13 2:47am

Mehdi Gholam

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...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Accepted Answer · 2013-03-04T05:14:00

Solution 3

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

Posted 4-Mar-13 5:14am

gvprabu

Comments

Petra Cerna 4-Mar-13 12:18pm

thanks, have never heard about this before, but it looks like a good solution