views:

71

answers:

6

i want to use a WILDCARD for everything in the statement beneath:

            "SELECT threads.*
 FROM thread_tag_map, threads, tags
 WHERE thread_tag_map.thread_id = threads.id
 AND thread_tag_map.tag_id = tags.id

 AND (tags.name IN ('WILDCARD'))";

What should I replace the WILDCARD with to be able to select every entry? I have tried ('*') but that doesnt work.

i could drop the last row but the thing is that i am making this to a function. passing a value lets say $value. and the $value will replace WILDCARD. so when i pass 'computer' as $value it will get all rows for computer. but then i want to be able to pass nothing and the default $value will be a wildcard to select everything. so how can i make this possible?

+2  A: 

If you really want to select every entry, drop the last condition altogether:

SELECT threads.*
  FROM thread_tag_map, threads, tags
 WHERE thread_tag_map.thread_id = threads.id
   AND thread_tag_map.tag_id = tags.id

Update (based on question update). Since you seem to be constructing SQL dynamically, you can check whether passed in tag value is null (or whatever you designated to be your "wildcard") and only omit the "AND tags.name = 'value'" condition then. Or, if by "function" you've meant MySQL's stored procedure, take a look at OMG Ponies' solution.

ChssPly76
A: 

You probably want to use LIKE which allows % and _ wildcards. IN tests inclusion into a set of values.

SELECT threads.*
        FROM thread_tag_map, threads, tags
        WHERE thread_tag_map.thread_id = threads.id
        AND thread_tag_map.tag_id = tags.id

        AND tags.name LIKE '%foo_bar%';
Dmitry
how should i type it then if i want to use it with LIKE?
weng
see the edit. You may also want start accepting answers, otherwise people will be not so eager to give them.
Dmitry
simplest one would be tags.name like '%' which catches every non null value
IronGoofy
I hope his question is not really about selecting all (not NULL) values. Who knows, though...
Dmitry
A: 
SELECT threads.*
FROM thread_tag_map, threads, tags
WHERE 1=1
AND thread_tag_map.thread_id = threads.id
AND thread_tag_map.tag_id = tags.id
AND tags.name != ''

Perhaps a query such as this, where you check if its not blank? That or drop the last AND.

meder
A: 

SELECT threads.* is selecting every column. To select every row you need to drop the last where condition:

This will select all columns and rows:

SELECT threads.* FROM thread_tag_map, threads, tags
    WHERE thread_tag_map.thread_id = threads.id
    AND thread_tag_map.tag_id = tags.id
Rob
+4  A: 

I recommend using MySQL's Prepared Statements to dynamically construct the SQL statement you want. Here's an example:

SET @SQL := 'SELECT th.*
              FROM THREADS th
              JOIN THREAD_TAG_MAP ttm ON ttm.thread_id = th.id
              JOIN TAGS t ON t.id = ttm.tag_id '

IF @tag_name IS NULL THEN

  PREPARE stmt FROM @SQL;
  EXECUTE stmt;

ELSE

  SET @SQL := @SQL + ' WHERE t.name LIKE ''%'+ @tag_name +'%'''
  PREPARE stmt FROM @SQL;
  EXECUTE stmt USING @tag_name;

END IF;
OMG Ponies
This approach also supports submitting a comma separated list of tag names as a string for an IN clause. Because of being a string, there's no problem concatenating into the existing SQL query before it is executed.
OMG Ponies
Forgot to mention that I changed the query to use ANSI JOINs
OMG Ponies
+1, though this is likely overkill unless OP's intended "function" is really a stored proc - both the check and prepare / execute can be (and, likely, are easier) done in whatever's language he's using.
ChssPly76
@Chss: Agreed, just a matter of concatenating strings - be it in MySQL or application.
OMG Ponies
+1  A: 

First, I think you're possibly misunderstanding the IN clause. IN lets you give a list of matching items:

SELECT * FROM [table] WHERE name IN ('Joe','Moe','Larry)


As mentioned by other people LIKE will allow pattern matching, with % being the multi-character wildcard (* in other languages, % here).

SELECT * FROM [table] WHERE name LIKE '%oe'


I'm assuming that in your case the $value is populated by the users, and so can be blank. in which case you want "everything". In an over simplified example, this is the kind ogf thing I've done in the past:

SELECT * FROM [table] WHERE (name LIKE @pattern OR @pattern = '')


As you appear to be building the SQL string up in the application, the alternative would be to only include the final WHERE condition if [LEN($value) > 0].

Also note, if you ARE taking a string from the user and imbedding it in the SQL to execute, make certain to use whatever methods you have to make that string safe. You don't really want someone to seach for the following string...

  • ')); DELETE tags WHERE (name NOT IN ('

SQL Injection is baaad, mmmkay?

Dems