SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Know any good SQL Server how to sites && question about conditional queries

    First, does anyone know any good SQL Server sites with articles and whatnot on query design, etc. Not so much basic "How to get data out of your table", but more complex topics like conditional stored procedures, working with triggers, etc. The MSDN is helpful but at times quite lacking in readibility (for me...I feel I never get an explaination of what they're doing and why it works, just "take this block of code").





    And second, any pointers (or links) on how I can go about having a stored procedure query a pair of product tables to get information to display in my shopping cart? I've got:

    Code:
    Cart Items            StdProducts         CustomProducts
    -----------          -------------       -----------------
    partID                   stdpartID            custompartID
    IsCustom (Bit)         
                             description          description
    So depending on if the 'IsCustom' field is True or False, I want to join the [cartitems].[partID] to either the [StdProducts].[stdpartID] or the [CustomProducts].[custompartID] to get the description and other information.

    Eventually, I'll probably need to branch this type of procedure out even further (not just either/or scenario) to include the option of pointing to 6-12 different child tables depending on criteria within the parent table. Depending on performance, I could either hard code in the various child tables or have another table containing the table names and the appropriate key that would indicate which table to use.

    Hopefully that made some sense, since I'm not entirely positive how to go about this type of thing or what to even search for on google.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    with just two tables, i'd use a simple join

    for six to twelve tables, i'd have to think about it

    in fact, i dunno if i would go to 6-12 tables, i might just keep everything in one

    whatever you do, don't store the table names

    do a search for "extended ER model" or "supertype and subtype"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    in fact, i dunno if i would go to 6-12 tables, i might just keep everything in one
    You'd use one table, even if it meant having to use generic field names like 'field1', 'field2', etc? I'm really looking at storing apples & oranges information. The data I need to capute is so specific (engineering specifications), that I feel like I need to have specific tables for the different classes of products.

    If I had to keep a listing of what information was in what field in what circumstances, I'd most likely go insane (although I might anyways).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, i would never name my columns 'field1' or 'field2'

    if they are apples and oranges, then yes, i might have separate tables

    but if they are products, don't they have any columns in common? product name?

    did you look up any supertype/subtype references?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Besides being products and having a price, part number and description they don't have much in common. I need to capture specific demensional data for each product. But something "long" won't need a radius field. Some items might have capacities, others won't. Some have an "overall height", others have a "usable height" and a "clearance height". Basically, each grouping of products will have similar dimensions, but different groups have little relation to each other.

    Ultimately how I figured I would deal with this is to have a [product] table that contained as much high level data as I could capture (part#, description, weight, price, ProductClassification) and then have a series of [ProductClassificationA] (and [...B], [...C] and so on) tables that I could then look to for the specific demension information I would need. The problem comes when I need rollup information across multiple subtables (for lack of a better word) in a single query. I'm totally lost on how to do this.



    I did look up the supertype/subtype stuff, but it seems like that is more for a recursive queries such as a bill of material, where you have Parent/ChildA as one db row, then ChildA/ChildB as another row within the same table and want to query this heirarchy all at once (like in bill of material). While I'll probably need this later, it doesn't really help me figure out how to reference multiple tables on a row by row basis (which I guess it what I need). Is this where a cursor would come into play? I certainly have no idea how to work with them....


    Thanks for your help,

    Chris
    Last edited by Chris F.; Jan 1, 2005 at 22:23.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, subtype/supertype is not the same as a bill-of-material hierarchy

    your "high level table" and series of specific tables is a good way to go, but not the only way

    when you need "rollup information across multiple subtables" you would typically be looking to roll up only the information that they have in common, so your query would really only need the main table

    also, don't be afraid of nulls

    the other way is to have just one table, and allow nulls
    Code:
    create table products
    ( product_code varchar(12) not null primary key 
    , price decimal(10,4) not null 
    , part_number varchar(9) not null
    , description varchar(100) not null
    , length varchar(9)
    , radius varchar(9)
    , capacity varchar(9)
    , overall_height varchar(9)
    , usable_height varchar(9)
    , clearance_height varchar(9)
    );
    rows for certain products would have some columns null, other products would have other columns null

    nothing wrong with that

    in fact, when you get right down to it, the multi-table and single table designs will have different SQL requirements (the multi-table design will obviously be more complex, involving many joins), but more importantly, they will have different application logic blocks, and what you have to do is try to visualize both the SQL and the accompanying application logic at the same time to see which is easier overall (i.e. if everythig is product type specific, versus if most queries are of the common rollup variety)

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I suppose it isn't so much the rollup part as the joining of one of two tables for the shopping cart.

    I had created a very similar site in the past and went with "allow nulls" philosophy. I quickly started getting overwhelmed with the number of fields I was using. My current project promises to be more complex (aka, have many more product groups). If each group has 20+ field and the site eventually grows to 10-15 groups, I think that's way too many (although I have no idea what actually limits would be).

    While you were posting that last bit, I had gone to add this to my previous post:

    The other thing I'm trying to do is keep duplicate tables of the different product tables for custom, user defined products. Basically, I'm allowing people to modify a standard part with their own dimensions. I'd prefer to keep the parts they create out of our item master completely so I was planning to give them their own table. I suppose I could just flag items that were user defined, but in the future the plan is to merge the online item master with the ERP's item master, and I definately don't want the one-off web orders showing as standard parts. (I should note that the ERP allows nonstandard items on a sales order than are not part of the item master which, as they define it, contains only standard parts). I can add custom fields to the ERP database, but I'm not about to modify their source to check if a product is standard or not.

    I think this is where my real issue is. When I have a pair of product tables, instead of a single product table, I'm not sure how I can have my shopping cart join to both. I'd prefer to have duplicate pricing information, etc. because those things might change and not update in a cart. The only thing I can come up with is to do out joins into both product tables and then add or concatenate the similar fields. For example, I'd have Cart OUTER JOIN productTableA and also OUTER JOINed to productTableB. If I have a price field with a number and another that is NULL, can I add them together and wind up with a single price (and can I convert this null to a decimal to do it)? Sounds ugly to me, there must be a better way.



    I'm trying to visualize how to do all this on both the DB side as well as the application side. I could probably do a pair of queries and combine them somehow in my application logic, but I imagine it would perform far better to have a single procedure which could return all the data I need, as opposed to manipulating datasets within the backend code.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you'd want an INNER join to the main product table, and an OUTER join to the optional table

    look up the COALESCE function, that's how you'd combine the prices (i.e. use the customer price, but if there isn't one, use the standard price)

    that is the better way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't I want an outer join to both, since the product will be in either table (but only one).

    I look up COALESCE in the morning...I'm way to tired to keep at it any more.

    Thanks for your help.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if it's either or, sure, you'd need OUTER for both

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    va
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris,
    For outer join info, check out this thread:
    http://www.sitepoint.com/forums/showthread.php?t=225408

    For other quality Sql Server links:
    http://www.geekgig.com/dir.aspx?ID=350
    http://www.GeekGig.com - Resources for the Geeks!

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the links. I'll be browsing the second one real soon.

    I'm not having any issues with the joins themselves but rather how to design the database schema and query functions that I'll need to use to basically do an inner join to two tables at once (probably not in the way you'd think).

    I need one set of information, but it could potentially be coming from two separate tables on a row by row basis. So I could either use the COALESCE function that Rudy mentioned or alternatively retrieve two queries of information and then combine them. I know how to do this within my .net dataset, but I'm not sure how to combine the query results within a stored procedure to output a single result set that would give me what I need. I would prefer to do the latter. I'm guessing this would be preferred to having to use a function on every field in the query and I would think it would be less server overhead than doing it the application.

    Thanks guys.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds like you might want a UNION query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    NJ
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes...that seems to be exactly what I need. Thanks (again).

    Chris


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •