How to search exact value in between two range in sql?

Question

I have those fields in my table

Invoice_number

Date

Item_code

Item_name

Qty

Total

I want to filter and get velues in between time period to a table in netbeans which are like

Example : Total sale of Item call item1 in between time period 2014.01.01 to 2014.03.01

my query i used is

        String sql="select sales_invoice_no,date,item_name,quantity,free,total FROM  sales_invoice2items where date between '"+repdate.getText()+"' and '"+repdate2.getText()+"'";
        Statement st1 = database.getconnection().prepareStatement(sql);
        ResultSet rs = st1.executeQuery(sql);

        reptable1.setModel(DbUtils.resultSetToTableModel(rs));

My code works fine it filter the values between time period but it doesnt filter the Item that i want please help.

Ah, the flirtations with [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) - you should really be using [parameterized queries](http://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program/9516653#9516653), or suffer the consequences. — Clockwork-Muse, May 01 '14 at 10:50

score 0 · Answer 1 · answered May 01 '14 at 09:16

it looks like you're not adding a filter:

This is your code:

String sql="select sales_invoice_no,date,item_name,quantity,free,total FROM  sales_invoice2items where date between '"+repdate.getText()+"' and '"+repdate2.getText()+"'";

Should it be:

String sql="select sales_invoice_no,date,item_name,quantity,free,total FROM  sales_invoice2items where Item_name = item1 and date between '"+repdate.getText()+"' and '"+repdate2.getText()+"'";

Thanx it works! Appriciate your help my bro. Thanx again – Bishan May 01 '14 at 10:35 — Bishan, May 01 '14 at 10:35

score 0 · Answer 2 · answered May 01 '14 at 09:16

0

Try as per below:

select 
sales_invoice_no,date,item_name,quantity,free,total 
FROM  sales_invoice2items 
where item_code='item1' and date between 'repdate1' and 'repdate2';

answered May 01 '14 at 09:16

Zafar Malik

6,734
2
19
30

Thanx it works! Appriciate your help my bro. Thanx again – Bishan May 01 '14 at 10:35

score 0 · Answer 3 · answered May 01 '14 at 09:16

0

It doesn't filter because you don't tell it to. Add the item name to the WHERE clause:

String sql="select sales_invoice_no,date,item_name,quantity,free,total "+
        "FROM  sales_invoice2items where date between '"+
        repdate.getText()+"' and '"+repdate2.getText()+"' and item_name='<<name>>'";

Also, please use prepared statements correctly, don't concatenate the values in, but send them apart from the query.

answered May 01 '14 at 09:16

Bart Friederichs

33,050
15
95
195

Thanx it works! Appriciate your help my bro. Thanx again – Bishan May 01 '14 at 10:35

How to search exact value in between two range in sql?

3 Answers3