Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Optional Inner Joins

    I have a select proc that will take a bunch or criteria parameters. Based on how many are not null I would like to decide how many inner joins to do.
    for example:

    select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId, H1.VendorPackageId from
    (
    select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
    (
    select VendorPackageId from ValidVendorPackages
    where Vendor = 'SBT'
    and Sitecode = 'system'
    and PackageType = 'AHCF'
    )HB on HA.VendorPackageId = HB.VendorPackageId
    and
    (
    CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = 'LAS'
    and LengthOfStay = 5
    and Ages = 'A2'
    and ComponentType = @ComponentType_1
    and ValidItemType = @ValidItemType_1
    and ItemValue = @ItemValue_1
    )
    )

    )H1 INNER JOIN
    (
    select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
    (
    select VendorPackageId from ValidVendorPackages
    where Vendor = 'SBT'
    and Sitecode = 'system'
    and PackageType = 'AHCF'
    )HB on HA.VendorPackageId = HB.VendorPackageId
    and
    (
    CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = 'LAS'
    and LengthOfStay = 5
    and Ages = 'A2'
    and ComponentType = @ComponentType_2
    and ValidItemType = @ValidItemType_2
    and ItemValue = @ItemValue_2
    )
    )
    )H2 on H1.Priceid = H2.priceId Inner Join
    (
    select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
    (
    select VendorPackageId from ValidVendorPackages
    where Vendor = 'SBT'
    and Sitecode = 'system'
    and PackageType = 'AHCF'
    )HB on HA.VendorPackageId = HB.VendorPackageId
    and
    (
    CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = 'LAS'
    and LengthOfStay = 5
    and Ages = 'A2'
    and ComponentType = @ComponentType_3
    and ValidItemType = @ValidItemType_3
    and ItemValue = @ItemValue_3
    )
    )
    )H3 on H2.Priceid = H3.priceId

    if values are only passed in from @ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner joins.

    If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 & @ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner Join.

    and of course if I get all 3 sets of criteria I want to do both the inner joins.
    I know I can cut and past this thing 3 times with an if statement but that isn't going to be practical.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you posting this again when you got responses the first time?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    Quote Originally Posted by blindman
    Why are you posting this again when you got responses the first time?
    This time it is much different. This time its not a matter of using the criteria (with an and clause). Your post was very helpfull in that matter. This post refers to the need to include all innerjoins if criteria is passed in, and not if criteria is not passed in. I don't believe you solution to my earlier post would address this issue. But again, thanks for your help.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not comfortable that I understand what you are trying to do. Based on the postings in the other thread on this topic, I'd be more likely to think that UNION operators are what you want instead of INNER JOINs.

    Can you give us more of the "10000 meter" view of what you are trying to accomplish without getting tied up in the details? I don't think that your problem is all that difficult to solve, but I can't get enough mental "traction" to be confident that I'm even on the right track, much less giving you good advice based on what I know now.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "I don't believe you solution to my earlier post would address this issue."???

    I believe it does...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2002
    Posts
    45
    Quote Originally Posted by Pat Phelan
    I'm not comfortable that I understand what you are trying to do. Based on the postings in the other thread on this topic, I'd be more likely to think that UNION operators are what you want instead of INNER JOINs.

    Can you give us more of the "10000 meter" view of what you are trying to accomplish without getting tied up in the details? I don't think that your problem is all that difficult to solve, but I can't get enough mental "traction" to be confident that I'm even on the right track, much less giving you good advice based on what I know now.

    -PatP
    Sure no problem. What we are doing is select criteria for a certain vacation. You can have slim criteria (not passing in componentType,ValidItemType,ItemValue) or you can be real specific passing in 1 to many set of criteria.
    For example:

    and ComponentType = 'H'
    and ValidItemType = 'C'
    and ItemValue = 'Las Vegas'

    Would specify that the Hotel has to be in the city Las Vegas.
    Just sending that criteria would bring back a broad range of hotels in that city. You can also additional criteria:

    and ComponentType = 'H'
    and ValidItemType = 'N'
    and ItemValue = 'Tropicana Las Vegas'

    This specifys that the Hotel has a name of Tropicana Las Vegas.

    I could go on and specify criteria about the room but I think you get the picture.

    so for every set of componentType,ValidItemType,ItemValue passed in I need to do another InnerJoin. It needs to be an Inner Join becasue if additional sets of criteria are sent in they need to be met. Doing a union would bring back the rows that meet each individual criteria set. We want to bring back the rows that meet all the criteria set.

    Let me know if you need additional Information.
    & Thanks for the help.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that we've come round full circle... Based on my understanding of the problem, there may be an application specific solution, but I don't see any generic solution.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is an odd way to pass in filtering parameters. Normally, if you want to filter on HotelName you just pass in a HotelName parameter.

    I don't understand what the header on you stored procedure is going to look like. What is the maximum number of parameter "triplets" you are going to accept?

    My honest opinion is that this is a matter of interpreting the user's request, and thus belongs within the scope of the interface, not the database engine.

    If you have to implement this interpretive logic through a stored proc, you may be best off using (gasp!) dynamic sql. (I am now going to crawl under my desk to shield myself from the torrent of posts that comment will provoke...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just to inject a little diversion, but are you saying you don't like my sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2002
    Posts
    45
    Quote Originally Posted by blindman
    This is an odd way to pass in filtering parameters. Normally, if you want to filter on HotelName you just pass in a HotelName parameter.

    I don't understand what the header on you stored procedure is going to look like. What is the maximum number of parameter "triplets" you are going to accept?

    My honest opinion is that this is a matter of interpreting the user's request, and thus belongs within the scope of the interface, not the database engine.

    If you have to implement this interpretive logic through a stored proc, you may be best off using (gasp!) dynamic sql. (I am now going to crawl under my desk to shield myself from the torrent of posts that comment will provoke...)
    Its not an odd way to pass in parameters, its a way to truly seperate the logic from the front end application and the workings of the database.
    The front end should be able to specify as much criteria as it wants. It sounds like I will have to write 7 stored procs to handle 1-7 different criteria types. I was trying to avoid this by writing 1 proc that would do all.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    knock, knock, knock...hello?

    Is this thing on?

    You've been a great audiance...don't forget the waitresses..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you won't need seven procs. Just one ugly proc with 21 parameters. Whether you use dynamic SQL or a string of left joins is up to you. I'd try the left join method first, but if it gets overly complex or has poor performance then try the dynamic SQL route (but as Brett hinted, watch out for SQL Injection attacks).

    But if your goal is to separate the front-end logic from the working of the database, what you are actually doing is forcing the front-end logic onto the database. And essentially, it is this design choice that is causing difficulties.

    That doesn't mean you can't do what you want to do, but that you have to be prepared for some complex SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is one more suggestion:

    If you want your front end to really be able to enter as many sets of parameters as the user wishes, then think about storing this criteria in a temporary table associated with the user's connection. Your procedure would then reference this temporary table, looping through each record and applying the appropriate filter or building the requisite dynamic SQL statement.

    This way, you avoid having all those potentially null parameters in your procedure header, and there is no limit to the criteria that can be applied.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2002
    Posts
    45
    Quote Originally Posted by blindman
    No, you won't need seven procs. Just one ugly proc with 21 parameters. Whether you use dynamic SQL or a string of left joins is up to you. I'd try the left join method first, but if it gets overly complex or has poor performance then try the dynamic SQL route (but as Brett hinted, watch out for SQL Injection attacks).

    But if your goal is to separate the front-end logic from the working of the database, what you are actually doing is forcing the front-end logic onto the database. And essentially, it is this design choice that is causing difficulties.

    That doesn't mean you can't do what you want to do, but that you have to be prepared for some complex SQL.
    This query has to be extremely fast, I've only shared the inners (Derived sql tables) of this sql statement. It actaully has horizontal partitioning on dates based on the priceid's = individual price per date.

    Speed is the main goal of this proc. I will still have 1 proc that accepts 21 parameters (with optional null values). This top level proc will check what has been passed in and decide which of the 7 procs to be called.

    Since I have your attention I was wondering if you knew if the following was possible:
    Can you select from a derived table created by a stored procedure?
    Example:
    Select * from
    (
    exec storedproc1
    ) a

    I know this syntex is no good, but was wondering if the theory was possible?

Posting Permissions

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