multiple record checking

[windowsxp]

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 ?

[K. Wolfe]

Can you elaborate? Supply sample data and what you want returned perhaps?

[windowsxp]

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 )

[K. Wolfe]

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

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.

[windowsxp]

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 ?

[K. Wolfe]

Yes. This is what I intend to do. Please elaborate how do I do this in if statement ?

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($result, MYSQL_NUM)) {
    echo $row[0] & " - ";
if ($row[0] >= 4 and $row[0] <= 7) {
echo "in desired range";
} else {
echo "not in desired range";
}
}

mysql_free_result($result); 

[windowsxp]

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($result, MYSQL_NUM)) {
    echo $row[0] & " - ";
if ($row[0] >= 4 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 ?

[K. Wolfe]

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.

[windowsxp]

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 ?

[K. Wolfe]

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.

[cpradio]

Since you are using MySQL you can utilize CASE

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

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