Retreiving records if the ff. field is on a list..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retreiving records if the ff. field is on a list..

Author

Topic

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 00:52:41

hello dudes. i've got the following table:


table name: assassins

fields:
assassinid       int, identity, primary key
assassinname     nvarchar
assassincallsign nvarchar
knifekills       int
sniperkills      int
stealthkills     int
fibergarotekills int

sample data:
1, Dana White, tapout, 12, 0, 12, 1
2, Chuck Liddell, iceman, 30, 0, 0, 2
3, Timothy Oliphant, FortySeven, 100, 20, 150, 30

supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page:
[] Knife Kills
[] Sniper Kills
[] Stealth Kills
[] Fibergarrote Kills

supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0

or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.

any of you know how to do that?

U + U + D + D + L + R + L + R + Sel + Start...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 01:09:40

Make bit field parameters corresponding to each of the checkboxes.
Get the values of checkboxes from you webpage using
form.querystring["chkknife"]; form.querystring["chksniper"]; ...
and pass these down to stored procedure as avlues for bit parameters
let them be @knife,@sniper....

then sp will be

CREATE PROC YourProcName
@knife bit=0,
@sniper bit=0,
@stealth bit=0,
@fbergarote bit=0,
... other params
AS
SELECT fields
FROM YourTable
WHERE (knifekills >0 OR @knife=0)
AND (stealthkills>0 or @stealth=0)
......
GO

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 01:18:29

ok sir, thank you for your reply. but is it possible to do it without using store procedure?

U + U + D + D + L + R + L + R + Sel + Start...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 01:20:12

You don't have to use stored procedure, but you will still need those variable to store the state of the checkbox.

KH
[spoiler]Time is always against us[/spoiler]

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 01:55:33

ok dudes, i've already got the idea of using visakh's without Store Procs. i just use CASE


SELECT CASE WHEN 'knifekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "knifekills",
CASE WHEN 'sniperkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "sniperkills",
CASE WHEN 'stealthkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "stealthkills",
CASE WHEN 'fibergarrotekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "fibergarrotekills"

assuming the the IN () clause list is dynamic...

but im receving errors when using those columnnames in the WHERE clause...

need help dudes...

U + U + D + D + L + R + L + R + Sel + Start...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 02:13:20

quote:
Originally posted by celeron

ok dudes, i've already got the idea of using visakh's without Store Procs. i just use CASE

SELECT CASE WHEN 'knifekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "knifekills",
CASE WHEN 'sniperkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "sniperkills",
CASE WHEN 'stealthkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "stealthkills",
CASE WHEN 'fibergarrotekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "fibergarrotekills"

assuming the the IN () clause list is dynamic...

but im receving errors when using those columnnames in the WHERE clause...

need help dudes...

U + U + D + D + L + R + L + R + Sel + Start...

You cant use alaises directly in WHERE clause. You need to make a derived table using main query and then access from it. like:-

SELECT *
FROM
(SELECT CASE WHEN 'knifekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "knifekills",
CASE WHEN 'sniperkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "sniperkills",
CASE WHEN 'stealthkills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "stealthkills",
CASE WHEN 'fibergarrotekills' in ('knifekills', 'sniperkills', 'stealthkills', 'fibergarrotekills') THEN '1' ELSE '0' END) "fibergarrotekills"
,...
)t
WHERE t.knifekills>0...

b/w i didnt understand need of multiple CASE statements though

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 03:04:05

ok nevermind sir. i have already got it to work:


select * from sample
where
(sample.knifekills > 0 and 'knifekills' in ('knifekills', 'stealthkills', 'headshots')) OR
(sample.stealthkills > 0 and 'stealthkills' in ('knifekills', 'stealthkills', 'headshots')) OR
(sample.headshots > 0 and 'headshots' in ('knifekills', 'stealthkills', 'headshots'))

i actually need multiple case since the list is actually dynamic (its actually based on the checkboxes you checked)... and i think it's the only way i could think of...

Thanks all for the advice!

U + U + D + D + L + R + L + R + Sel + Start...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 03:25:58

quote:
Originally posted by celeron

ok nevermind sir. i have already got it to work:

select * from sample
where
(sample.knifekills > 0 and 'knifekills' in ('knifekills', 'stealthkills', 'headshots')) OR
(sample.stealthkills > 0 and 'stealthkills' in ('knifekills', 'stealthkills', 'headshots')) OR
(sample.headshots > 0 and 'headshots' in ('knifekills', 'stealthkills', 'headshots'))

i actually need multiple case since the list is actually dynamic (its actually based on the checkboxes you checked)... and i think it's the only way i could think of...

Thanks all for the advice!

U + U + D + D + L + R + L + R + Sel + Start...

Sorry but i don't really understand your query here. Are you sure you got want you want ? ?
this statement will forever be true

'knifekills' in ('knifekills', 'stealthkills', 'headshots')

Isn't visakh solution works for you ?

KH
[spoiler]Time is always against us[/spoiler]

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 03:44:04

i havent tried his solution since im not supposed to use stored procs but i got the idea from the sample code he gave...


'knifekills' in ('knifekills', 'stealthkills', 'headshots')

i used that code to know if the word 'knifekills' is in the list, and if it is, and if the field 'sample.knifekills' is greater than 0 then it means that i have to retreive that data since the user would have wanted it displayed... take note that that list is from a bunch of checkboxes that basically represents the Type Of Kills that the user wants shown.

oh, i actually forgot to tell that sql query code is "assembled" in java...


        String[] assassins;

        assassins = request.getParameter["chkKillTypes"]; //get selected checkboxes

        strAssassins = new StringBuffer();

        strAssassins.append(" IN (");

        for (x = 0; x < assassins.length; x++) {
            strAssassins.append(assassins[x].toString());

            if (x + 1 < assassins.length) {
                strAssassins.append(", ");
            }
        }

        strAssassins.append(")");

        strQuery.append("SELECT * FROM sample WHERE sample.knifekills > 0 and 'knifekills' IN ");
        strQuery.append(strAssassins + ", ");
        strQuery.append("sample.stealthkills > 0 and 'stealthkills'  ");
        strQuery.append(strAssassins + ", ");
        strQuery.append("sample.headshots > 0 and 'headshots' ");
        strQuery.append(strAssassins );

        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ntms");
        Connection con = ds.getConnection();

        Statement stmnt = con.createStatement();
        ResultSet rs = stmnt.executeQuery(strQuery.toString());

U + U + D + D + L + R + L + R + Sel + Start...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 03:49:27

quote:
Originally posted by celeron

i havent tried his solution since im not supposed to use stored procs but i got the idea from the sample code he gave...

'knifekills' in ('knifekills', 'stealthkills', 'headshots')

i used that code to know if the word 'knifekills' is in the list, and if it is, and if the field 'sample.knifekills' is greater than 0 then it means that i have to retreive that data since the user would have wanted it displayed... take note that that list is from a bunch of checkboxes that basically represents the Type Of Kills that the user wants shown.

U + U + D + D + L + R + L + R + Sel + Start...

I just showed you the logic in stored procedure. if you dont want to use sps just run it as a query

SELECT fields
FROM YourTable
WHERE (knifekills >0 OR {chkKnife.value}=0)
AND (stealthkills>0 or {chkstealth.value}=0)
......

replace the chk.value in blue code with variable used for storing corresponding checkbox values.

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 04:05:21

quote:
Originally posted by visakh16

quote:
Originally posted by celeron

i havent tried his solution since im not supposed to use stored procs but i got the idea from the sample code he gave...

'knifekills' in ('knifekills', 'stealthkills', 'headshots')

i used that code to know if the word 'knifekills' is in the list, and if it is, and if the field 'sample.knifekills' is greater than 0 then it means that i have to retreive that data since the user would have wanted it displayed... take note that that list is from a bunch of checkboxes that basically represents the Type Of Kills that the user wants shown.

U + U + D + D + L + R + L + R + Sel + Start...

I just showed you the logic in stored procedure. if you dont want to use sps just run it as a query

SELECT fields
FROM YourTable
WHERE (knifekills >0 OR {chkKnife.value}=0)
AND (stealthkills>0 or {chkstealth.value}=0)
......

replace the chk.value in blue code with variable used for storing corresponding checkbox values.

yeah i know, but im using Struts Framework, and my only access to the checkbox values is through the ActionForm for this particular module...

don't worry dudes, its already working thanks to your helps...

U + U + D + D + L + R + L + R + Sel + Start...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 04:07:02

quote:
Originally posted by celeron

quote:
Originally posted by visakh16

quote:
Originally posted by celeron

i havent tried his solution since im not supposed to use stored procs but i got the idea from the sample code he gave...

'knifekills' in ('knifekills', 'stealthkills', 'headshots')

i used that code to know if the word 'knifekills' is in the list, and if it is, and if the field 'sample.knifekills' is greater than 0 then it means that i have to retreive that data since the user would have wanted it displayed... take note that that list is from a bunch of checkboxes that basically represents the Type Of Kills that the user wants shown.

U + U + D + D + L + R + L + R + Sel + Start...

I just showed you the logic in stored procedure. if you dont want to use sps just run it as a query

SELECT fields
FROM YourTable
WHERE (knifekills >0 OR {chkKnife.value}=0)
AND (stealthkills>0 or {chkstealth.value}=0)
......

replace the chk.value in blue code with variable used for storing corresponding checkbox values.

yeah i know, but im using Struts Framework, and my only access to the checkbox values is through the ActionForm for this particular module...

U + U + D + D + L + R + L + R + Sel + Start...

Can you illustrate how will take the values?

celeron
Starting Member

11 Posts

Posted - 2008-06-23 : 04:21:26

[code]
public ActionForward show(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception {

AssassinForm af = (AssassinForm) form;
DBManager dbm = new TDBManager();

af.setAssassinsList(dbm.getAssassins(af.getSelectedKillTypes());

return mapping.findForward("showForm");
}
[/code]

the return type of af.getSelectedKillTypes() is an array String...

don't worry dudes, its already working thanks to your helps...

U + U + D + D + L + R + L + R + Sel + Start...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources