where column = any

[TechBoyJK]

What is the correct syntax for saying you want any record? I believe you could just delete the "where" clause altogether, but I want to make that part of the query variable, and it may not always be "any". Is there a way to do this, or do I have to just delete the where clause?

I'd want the code to read something like this:

SELECT member_id, member_info, member_title, Member_created
FROM tbl_member_table_1
Where member_id = any
;

 

[Train]

To grab all the records, just drop the where clause.

If you want to make it a "variable" I guess you could do something like

where member_id != null

which, assuming its not a nullable field, will get all records every time.

EDIT:

it would probably be slightly faster to do this, and will work for nullable fields as well:

where 1 = 1

 

[kyzen]

Just drop there where clause

 

[TechBoyJK]

Originally posted by: kyzen
Just drop there where clause

I can do that...

However, I'm trying to leave the where clause in teh code.

Reasoning, is that I want to pass a variable for the where clause

where member_id = #variable#

So that if I want to use that query to say "where member_id = 146" or "where member_id = any"

 

[Train]

your just gonna have to put a little logic in where your building your query string, if where clauses > 0, then put the where clause in, else, skip it.

 

[HardcoreRobot]

sounds like you need 2 queries

 

[kyzen]

2 queries are definitely not necessary. code below for SQL server, you may need to adapt the syntax for Access (I dunno how Access treats parameters/variables):

SELECT member_id, member_info, member_title, Member_created
FROM tbl_member_table_1
WHERE ( member_id = @ParamVal OR @ParamVal is null )




(replace the " is null " part with "= 0 " or " = ' ' " or whatever non-value qualifier you opt to use)

 

[Ken g6]

Ah, been there, done that, but can't remember where at the moment. One way would be "Where member_id like '%'"; but beware that this won't match null, and I'm not sure about an empty string.

 

[KLin]

Just put in some logic that checks the value of the variable. If it's null(or an empty string), don't include a where clause. Otherwise, include the where clause with the variable value.

 

[KIAman]

where member_id like @ParamVal

if you want all matches, set @ParamVal = %

 

[Superwormy]

SELECT * FROM table WHERE 1

The query optimizer will optimize it out and remove it anyway, so it won't hurt performance.

Or:

SELECT * FROM table WHERE member_id IN ( SELECT member_id FROM table )

 

[imported_Dhaval00]

Originally posted by: Superwormy
SELECT * FROM table WHERE 1

The query optimizer will optimize it out and remove it anyway, so it won't hurt performance.

Or:

SELECT * FROM table WHERE member_id IN ( SELECT member_id FROM table )

Not to nitpick, but it's ironic how you mention the optimizer, and then propose using "IN." 🙂.

Just thought was humorous.

 

[drebo]

The fastest way to do this is by having the application check whether or not the variable is set and then either not including or including the where clause accordingly.

 

[KLin]

Originally posted by: drebo
The fastest way to do this is by having the application check whether or not the variable is set and then either not including or including the where clause accordingly.

I wholeheartedly agree with this statement.