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 |
|
alecd
Starting Member
7 Posts |
 Posted - 2007-08-30 : 20:22:36
|
| Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-30 : 20:32:09
|
| Sounds like you might be creating a bit of a mess there and working a lot harder than you need to, but it is hard to tell without all of the specifics. If you give some clear, concrete examples of some of the data you are modeling and how you've chosen to store it, perhaps we can offer some guidance.Anyway, remember that a JOIN is done by using boolean expressions; it doesn't always have to be TABLE1.COL = TABLE2.COL. So, you can join two tables on any valid boolean expression, such as TABLE1.COL1 = TABLE2.COL1 AND TABLE1.COL2 = TABLE2.COL2or evenTABLE1.COL1 = 'JEFF' AND TABLE1.COL2 = TABLE1.COL2 AND TABLE2.COL3 > 0and so on ...- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
Inner Join