Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Join only if condition is met

    Dear All,

    I have two tables in the following structure;

    Rule table;

    SelectionID Lifetime Bonus
    1 2 0
    2 1 0
    3 3 0


    Player table (one record per playerid);

    Playerid Lifetime Bonus
    1 3 1
    2 3 2
    3 2 2

    Initially I would like to retrieve the selectionid for each playerid where the criteria lifetime is equal i.e

    select player_table.playerid, rule_table.slectionid
    from
    player_table, rule_table
    where
    player_table.lifetime = rule_table.lifetime


    Results in;
    playerid selectionid
    1 3
    2 3
    3 1


    I now would like to include the bonus as below but only is the bonus field in rule_table is not 0.

    select player_table.playerid, rule_table.slectionid
    from
    player_table, rule_table
    where
    player_table.lifetime = rule_table.lifetime
    and
    player_table.bonus = rule_table.bonus

    Reults in zero results.

    I would like to reference rule_table.bonus and because it is 0 ignore player_table.bonus = rule_table.bonus.

    And get the result;

    playerid selectionid
    1 3
    2 3
    3 1


    I need to do this because in reality I have 20 factor to make but only if if they contain a specif value in reul_table.

    I hope I have been clear in my explanation.

    Many thanks in advance.

  2. #2
    Join Date
    Apr 2014
    Posts
    2

    Only Join when criteria met

    I have twe tables

    Rule;

    Selectionid Lifetime Current
    1 1 0
    2 2 0
    3 3 0

    Player;
    Playerid Lifetime Current
    1 2 1
    2 3 4
    3 1 0

    I would like to match the playerid to the mailerid;
    Select playerid, selectionid
    From player, rule
    Where
    Player.lifetime = rule.lifetime

    Correct Result;
    Playerid Selectionid
    1 2
    2 3

    I would like to now include current into the sql

    Select playerid, selectionid
    From player, rule
    Where
    Player.lifetime = rule.lifetime
    And
    Player.current = rule.current

    This would return no results. I would like to only use Player.current = rule.current when player.current is not 0. I am asking this because in reality I have to match on 20 criteria but only if there is a value > 0 in my rule table.

    I hope my explanation is clear. Many thanks in advance.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sorry, but I don't understand what you're trying to accomplish. What you've stated in your post sounds like a straightforward SQL JOIN operation. I don't understand how this is different from any other join.

    I'd suggest that you put the SQL aside for a moment. Try to explain what you are trying to accomplish in terms of the real world instead of focusing on how to write the SQL statements. Maybe that will help me to understand what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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