Dynamic INNER 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 2005 Forums

Transact-SQL (2005)

Dynamic INNER JOIN?

Author

Topic

bobbydiamondz
Starting Member

5 Posts

Posted - 2007-05-16 : 14:14:06

Hi,

To try and make this simple, assume I have three tables. A product table, and a category table, and a relational product_categories table. Products to categories is a many to many relationship. I have a stored procedure that accepts a parameter @categoryid. By default that parameter is set to null. If the parameter is provided I need to do an INNER JOIN on the product_categories table with productid. Otherwise, if the paramter is not provided, I don't want to JOIN on that relational table at all. Right now I'm using an IF statement to check for the null case and build my query accordingly. It works great for now. However, more relational tables are planned, and the IF statement is going to become a mess quickly. Is there another way to dynamically perform INNER JOINs without using dynamic SQL? I hope this makes sense, if not I'll try to clarify.

Regards,
Bob

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-16 : 14:25:40

Sounds like bad design from the get go. Do you REALLY need to dynamically build your queries? REALLY REALLY?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

bobbydiamondz
Starting Member

5 Posts

Posted - 2007-05-16 : 14:33:57

That's how it was handed to me. I don't have to do it any certain way if I want to start over. Maybe I should before it gets out of hand.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 14:51:23

See if this helps:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

The key is: your SQL statements should be static. The data that is ultimately returned should always have the same structure. You can use CASE and conditions to alter the calculations, but the row/column structure of the data returned should always be consistent. At your front-end, where the data is ulatimately displayed, you can hide or show certain columns as desired, but at the database layer, your SQL commands should rarely be dynamic.

- Jeff
http://weblogs.sqlteam.com/JeffS

bobbydiamondz
Starting Member

5 Posts

Posted - 2007-05-16 : 16:28:05

Hi Jeff,

Thanks for the response. I read through the linked article, and it sounds promising, but I'm not sure it will work with what I need to do. My original description was pretty bad. I'll try to be clearer this time:

I'm trying to search the products table by a certain parameter. For example let's just say product_name. So the SP accepts a parameter for the product_name and a parameter for a categoryid. If no categoryid is supplied, I want to only return results from the products table. However, if a categoryid is supplied, then I need to filter my results with the product_categories relational table so that only products that belong to that category get returned. Does that make more sense?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 16:35:31

I am not sure that your requirements make sense; the way you describe it, the productname is always required, so won't that always need to match? Or is the product name optional as well?

Assuming that the @productName parameter is also optional (i.e., only a @categoryID is passed in), then something like this is all you need:


select 
  p.*
from 
  products p
inner join
     (select productID from  
      product_Category
      where (@CategoryID is null OR CategoryID = @CategoryID)
      group by productID
     ) pc on p.productID = pc.productID
where 
  (@productName is null OR p.productName = @productName)

also, sounds like you might need to use LIKE if you are matching/searching on product names ....

The first step before you or anyone else can write a single line of SQL code is to very clearly and completely define and state your specifications to indicate exactly what you want your code to do. Only when that is done can the code be written. Right now, there's lots of guesswork going on.

- Jeff
http://weblogs.sqlteam.com/JeffS

bobbydiamondz
Starting Member

5 Posts

Posted - 2007-05-16 : 16:48:05

Hi Jeff,

Thanks again for the response. Yes, @productName is optional, and I am using LIKE to match product names. I apologize again for the lame sample example, but the actual query I inherited was too complex for me to describe accurately. Thanks for taking the time to respond. I think your example is what I need.

- Bob

bobbydiamondz
Starting Member

5 Posts

Posted - 2007-05-16 : 18:28:29

I finally got it working. This statement only joins on ProductCategories if @categoryid is supplied. Just incase someone in need stumbles upon this post, here it is:

SELECT p.*
FROM Products p
WHERE productname LIKE @ productname + '%'
AND (@categoryid IS NULL OR EXISTS (SELECT * FROM ProductCategories pc WHERE p.id = pc.productid AND pc.categoryid = @categoryid))

I found the answer here:
http://www.sommarskog.se/dyn-search.html#complexconditions

Thanks to Jeff & Don for responding. I appreciate the help.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-17 : 08:12:25

Give all the credit to Jeff. I give responses like I code. Minimal. Jeff gave you the verbose explanation, and ultimately, the correct path to follow.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources