Getting all results using where clause

Question

I have a function which takes an argument that is used in where clause

function(string x)-->Now this will create a sql query which gives

select colname from tablename where columnname=x;

Now I want this function to give all rows i.e. query equivalent to

select colname from tablename;

when I pass x="All".

I want to create a generic query that when I pass "All" then it should return me all the rows else filter my result.

score 15 · Accepted Answer · answered Jul 27 '12 at 06:22

15

Just leave the where condition out.

If you really want it that complicated use

where columnname LIKE '%'

which will only filter nulls.

answered Jul 27 '12 at 06:22

stracktracer

1,862
5
24
37

Joe G Joseph · Answer 2 · 2012-07-27T06:33:03.630

7

select colname from tablename 
where columnname=(case when @x ="All" then columnname
                  else  @x end)

edited Jul 27 '12 at 06:33

answered Jul 27 '12 at 06:27

Joe G Joseph

23,518
5
56
58

score 6 · Answer 3 · answered Jul 27 '12 at 06:32

6

Try this

select colname from tablename where 1=1

hope the above will work

answered Jul 27 '12 at 06:32

Esh

836
5
16
43

score 2 · Answer 4 · answered Jan 05 '20 at 15:53

2

where 1=1 worked for me, Although where clause was being used all records were selected.

You can also try

[any_column_name]=[column_name_in_LHL]

(LHL=left hand side.)

refer my answer for more details

answered Jan 05 '20 at 15:53

saswat panda

151
1
5

score 1 · Answer 5 · answered Nov 29 '16 at 19:51

1

I had the same issue some time ago and this solution worked for me

select colname from tablename where columnname=x or x = 'ALL'

answered Nov 29 '16 at 19:51

Ruben Picart

11
1

Care to elaborate? – RamenChef Nov 29 '16 at 20:09
The "or" in the where clause is doing the trick here. If the condition columnname = x is met then the where clause filters the results. Otherwhise if the x = 'ALL' then the where clause is bypassed. Thus removing the filter. – Ruben Picart Apr 26 '17 at 13:51

score 1 · Answer 6 · answered Feb 06 '19 at 10:13

SELECT * FROM table_name WHERE 1;
SELECT * FROM table_name WHERE 2;
SELECT * FROM table_name WHERE 1 = 1;
SELECT * FROM table_name WHERE true;

Any of the above query will return all records from table. In Node.js where I had to pass conditions as parameter I used it like this.

const queryoptions = req.query.id!=null?{id : req.query.id } : true;
let query = 'SELECT * FROM table_name WHERE ?';
db.query(query,queryoptions,(err,result)=>{
res.send(result);
}

ross_t · Answer 7 · 2012-07-27T06:28:51.320

If you have to allow 'ALL' to be passed through as the parameter value to your function, then you will need to put some manipulation code in your function to construct your SELECT statement accordingly. I.e. You can detect if the parameter has 'ALL' in it and then omit the WHERE clause from your SQL statement. If a value other than 'ALL' comes through, then you can include the WHERE clause along with the relevant filter value from the parameter.

An example of a piece of code to do this would be;

IF x = 'ALL'
THEN
   SELECT COLNAME FROM TABLENAME;
ELSE
   SELECT COLNAME FROM TABLENAME WHERE COLUMNNAME = X;
END IF;

score 0 · Answer 8 · answered Jul 27 '12 at 06:24

0

It's unclear what language you're using for your function, but you have to somehow parse the 'All' prior to getting to sql:

public void query(String param) {
  String value = "":
  switch (param) {
    case 'All':
      value = "*";
      break;
    default:
      value = param;
  }
  String sql = "select colname from tablename where colname="+value;
  //make the query
}

answered Jul 27 '12 at 06:24

Nathaniel Ford

20,545
20
91
102

It is not explicit, but reading between the lines they appear to be using PL/SQL in MySQL. – ross_t Jul 27 '12 at 06:30

score 0 · Answer 9 · answered Jul 27 '12 at 06:29

0

Give a conditional check in your code(assuming Java) to append the WHERE clause only when x != 'All'

mySqlQuery = "SELECT colname FROM tablename" + 
                (x.equals("All") ? "" : "WHERE columnname = "+x);

answered Jul 27 '12 at 06:29

Subodh

2,204
18
22

Getting all results using where clause

9 Answers9

Linked