SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple record checking

    in my store procedure select query returning multiple records for a column.


    I want to write a if condition for the column values matching with few specific values. Is it possible ?

    How it is done ?

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Can you elaborate? Supply sample data and what you want returned perhaps?

  3. #3
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Can you elaborate? Supply sample data and what you want returned perhaps?

    Select
    os_group.ROLE_SL_NO
    From
    os_user Inner Join
    os_membership On os_membership.USER_SL_NO = os_user.USER_SL_NO Inner Join
    os_group On os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO
    Where
    os_user.USER_SL_NO = userid



    Now how do I capture ROLE_SL_NO from this select query and check whether it contains ROLE_SL_NO = 4 OR 5 OR 6 OR 7 ?

    I'm stuck at this part. Need help.

    (N.B: I need to know this because I'll write a if-else condition )

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by windowsxp View Post
    Select
    os_group.ROLE_SL_NO
    From
    os_user Inner Join
    os_membership On os_membership.USER_SL_NO = os_user.USER_SL_NO Inner Join
    os_group On os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO
    Where
    os_user.USER_SL_NO = userid



    Now how do I capture ROLE_SL_NO from this select query and check whether it contains ROLE_SL_NO = 4 OR 5 OR 6 OR 7 ?

    I'm stuck at this part. Need help.

    (N.B: I need to know this because I'll write a if-else condition )
    Will return only those with os_group.ROLE_SL_NO equal to 4, 5, 6,or 7
    Code:
    Select
    os_group.ROLE_SL_NO
    From
    os_user Inner Join
    os_membership On os_membership.USER_SL_NO = os_user.USER_SL_NO Inner Join
    os_group On os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO
    Where
    os_user.USER_SL_NO = userid AND
    os_group.ROLE_SL_NO IN (4,5,6,7)
    Otherwise, if you dont want to limit your results to those, then just do an if statement in your server side programming to handle the logic.

  5. #5
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Will return only those with os_group.ROLE_SL_NO equal to 4, 5, 6,or 7


    Otherwise, if you dont want to limit your results to those, then just do an if statement in your server side programming to handle the logic.
    Yes. This is what I intend to do. Please elaborate how do I do this ?

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by windowsxp View Post
    Yes. This is what I intend to do. Please elaborate how do I do this in if statement ?
    PHP Code:
    mysql_connect("localhost""mysql_user""mysql_password") or
        die(
    "Could not connect: " mysql_error());
    mysql_select_db("mydb");

    $result mysql_query("Select os_group.ROLE_SL_NO From os_user Inner Join os_membership On os_membership.USER_SL_NO = os_user.USER_SL_NO Inner Join os_group On os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO Where os_user.USER_SL_NO = userid");

    while (
    $row mysql_fetch_array($resultMYSQL_NUM)) {
        echo 
    $row[0] & " - ";
    if (
    $row[0] >= and $row[0] <= 7) {
    echo 
    "in desired range";
    } else {
    echo 
    "not in desired range";
    }
    }

    mysql_free_result($result); 

  7. #7
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    PHP Code:
    mysql_connect("localhost""mysql_user""mysql_password") or
        die(
    "Could not connect: " mysql_error());
    mysql_select_db("mydb");

    $result mysql_query("Select os_group.ROLE_SL_NO From os_user Inner Join os_membership On os_membership.USER_SL_NO = os_user.USER_SL_NO Inner Join os_group On os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO Where os_user.USER_SL_NO = userid");

    while (
    $row mysql_fetch_array($resultMYSQL_NUM)) {
        echo 
    $row[0] & " - ";
    if (
    $row[0] >= and $row[0] <= 7) {
    echo 
    "in desired range";
    } else {
    echo 
    "not in desired range";
    }
    }

    mysql_free_result($result); 
    I did not get this . I'm not using PHP. I'm getting confused with this.

    I'm writing store procedure. let me explain my difficulty . my difficulty is how do I capture[/B] ROLE_SL_NO from select query and check whether it contains ROLE_SL_NO = 4 OR 5 OR 6 OR 7 inside store procedure ?

    I need a relevant piece of code for doing this logic inside store procedure.


    do I need to declare a variable in store proc to do this ?

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Well I guess I'm confused on what you plan on doing with it then. You can build a a new boolean column using a case statement, but I don't see this as necessary.

    http://dev.mysql.com/doc/refman/5.0/en/case.html

    What is the final result you want? What are you trying to accomplish? I really don't feel that this should be needed until you go to actually do something with the data.

  9. #9
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post

    What is the final result you want? What are you trying to accomplish? I really don't feel that this should be needed until you go to actually do something with the data.
    my plan is like this ..

    just logic

    if (role_sl_no=4 or 5 or 6 or 7) then
    //do something
    else
    //do another thing


    I am stuck how to capture the roles_sl_no from select query and check in this if condition. Can you help at this part ?

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Ah I see. I personally do not have experience with stored procedures like this, for a few reasons. I've always had my front end handle any "logic" to be done, not the db. The benefit of this is that when your application reaches this stage, it is replication safe (though all apps / dbs should be treated for this). Stored procedures break replication.

    I'm sure that someone here could help you with an SQL stored procedure, though my recommendation is to steer clear and find a front end language to handle logic such as this.

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Since you are using MySQL you can utilize CASE

    Code MySQL:
    SELECT
      CASE
        WHEN os_group.ROLE_SL_NO >= 4 AND os_group.ROLE_SL_NO <= 7 THEN 1
        ELSE 0
      END AS 'IsInRole'
    FROM os_user 
      INNER JOIN os_membership ON os_membership.USER_SL_NO = os_user.USER_SL_NO 
      INNER JOIN os_group ON os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO
    WHERE
      os_user.USER_SL_NO = userid

    Or if you really want to do logic, you can use the following


    Code MySQL:
    DECLARE v_ROLE_SL_NO AS INT
     
    SELECT
      os_group.ROLE_SL_NO INTO v_ROLE_SL_NO
    FROM os_user 
      INNER JOIN os_membership ON os_membership.USER_SL_NO = os_user.USER_SL_NO 
      INNER JOIN os_group ON os_membership.ROLE_SL_NO = os_group.ROLE_SL_NO
    WHERE
      os_user.USER_SL_NO = userid
     
    IF v_ROLE_SL_NO >= 4 AND v_ROLE_SL_NO <= 7 THEN
     -- Has a role of 4, 5, 6, or 7
    ELSE
     -- Is not a role of 4, 5, 6, or 7
    END IF
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


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
  •