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.
| Author |
Topic |
|
bustesj
Starting Member
2 Posts |
 Posted - 2004-01-06 : 14:40:57
|
| First off great article on dynamic queries in the where clause using coalesce. However, lets say I also want to join tables based on input. For example, I have a tables named servers, software and serversoftware_xrf. The xrf table has servers.serverid and software.softwareid as a crossreference. I want to find all servers with softwareid 100 installed. Normally I would just cross reference the tables, but I want the query to be dynamic with other columns available to the end user. What is the cleanest way to do this?Thanks |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-06 : 14:51:14
|
| I don't see what the issue is. You just JOIN the tables together. Or are you saying that you want WHICH tables to be joined and what fields they are joined on to be dynamic? If so, I suggest you rethink that idea because that's a really cool way to give your users the ability to hang your server. Imagine that they pick fields that don't go together and end up producing a CROSS JOIN producing millions of rows of data that doesn't really relate.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
bustesj
Starting Member
2 Posts |
Posted - 2004-01-06 : 15:07:22
|
| The table and columns to join on are static, it's just whether to do the actual join or not that needs to be dynamic. Below is what I am thinking it looks like now, there has got to be a better way to do it (I hope).softwareid (variable to be joined on)if(softwareid>0) select columns + the joinselse select just the columns with no join.I am wanting to know if there is a way to get rid of the if statement because it could become very messy. The article on coalesce was cool, is there something like that?Thanks |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-01-06 : 15:30:33
|
| Build the two views and depending on what the user requests access the appropriate view.vw_ViewWithoutJoinsvw_ViewWithJoins |
|
|
|
|
|
|
Dynamic Query across tables