optional join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

optional join

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-07-03 : 03:56:54

Hi,

In my query I have a set of 10+ optional params.
Based on the value of those, I want to either inner join or not join an equal amount of tables.
All I could find is this unanswered post [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59872&SearchTerms=optional,join[/url] and a bunch of tuturials telling me how to either join on A or join on B based on my parameter.

I could do it with dynamic SQL, but that's ugly and can be dangerous even.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 04:08:40

can you give us an example on what do you mean by that ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 04:22:48

If i understand you correctly this is what you want. For illustration purpose i'll include only join with single table. you can extend it to 10 tables in your case. Suppose your tables be maintable and table1.let param be @Param and if value of @param is value1 you want to join onto the table1 else not. you could do this like below:-

SELECT t1.*
FROM maintable m
LEFT JOIN table1 t1
ON t1.linkingcolumn=m.linkingcolumn
WHERE (t1.linkingcolumn IS NOT NULL OR @param<>value1)

this will give you what you want unless you've one to many relationship between maintable and table1.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-07-03 : 05:35:27

Thanks for your replies.
Sadly, yes.. I have several m-2-m relations.
To illustrate:
Color
-----
-id
-code
-coId
Company
-------
-id
-name
CompanyModels
-------------
-companyId
-modelId
Models
------
-modelId
-modelName
ColorApplications
-----------------
-colorId
-applicationCode
Application
-----------
-applicationCode
-applicationDesc

@colorCode
@companyName
@modelName
@applicationDescription

All, none or some of the parameters can be null.
Color table has near 100k rows, company nearly 1k, models several thousand and application 10+.
What I always have to return is the colorId, colorCode, colorName and companyName.
The applicationDescription should be filtered on if supplied, but not returned.

Hope that makes things a bit more clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 05:58:22

quote:
Originally posted by BorisCallens

Thanks for your replies.
Sadly, yes.. I have several m-2-m relations.
To illustrate:
Color
-----
-id
-code
-coId
Company
-------
-id
-name
CompanyModels
-------------
-companyId
-modelId
Models
------
-modelId
-modelName
ColorApplications
-----------------
-colorId
-applicationCode
Application
-----------
-applicationCode
-applicationDesc

@colorCode
@companyName
@modelName
@applicationDescription

All, none or some of the parameters can be null.
Color table has near 100k rows, company nearly 1k, models several thousand and application 10+.
What I always have to return is the colorId, colorCode, colorName and companyName.
The applicationDescription should be filtered on if supplied, but not returned.

Hope that makes things a bit more clear.

ok. i dont think it wil matter much in your case as you are trying to display the unique valued fields. so you can very well try using my method.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-07-03 : 06:17:07

Correct me if I'm wrong, but the method you propose, doesn't it imply it will first do the joining anyway and then filter the unmatching ones out again?

The problem I'm facing is, that combinding 100k rows with 1k rows ends up in monsters of resultsets.
If no filtering nor returning of the values in those extra fields will be done, it would be usefull to not join the tables to begin with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources