MySQL Query

Need something similar to IN('1', '2')

[Nutter]

I'm working on a database query that is confusing me, which is easy (the confusion, not the query).

There are two tables, let's call them tblA and tblB. Each of these tables has a field that is delimited with different ids (tied to another table). What I'm looking for is a way to add a clause where records are only returned if one of the members in tblA.field matches a member in the tblB.field.

There will be other WHERE clauses, so ideally I need something that can stack.

I'm using MySQL, and I think it's a version that pre-dates subqueries if that makes a difference.

Thanks,
- Ryan

[dmorison]

Just for clarification, you mean that tblA.field might contain:

23,45,65,34

..and tblB.field might contain:

3,46,65,12

..and you want to SELECT from tblA,tblB WHERE, amongst other WHERE clauses, tblA.field contains AT LEAST one element from tblB.field (where the "elements" are the comma separated numbers within a varchar or whatever type field)?

If that is correct, then to my knowledge there is absolutely no native SQL or even MySQL extension that will be able to do this efficiently, if at all.

Can you consider doing this at the application level?

[Nutter]

Your clarification is correct.

I'm working on it in the application right now. I'm adding a WHERE (field LIKE '%code1%' OR field LIKE '%code2%') at the end.

Each field should only max out at around 5 different codes, so I'm hoping it won't be too much of a performance hit.

And, since I forgot at the beginning, THANKS

[dmorison]

If you do start to see a performance problem you might want to look at MySQL's full text indexing and search features.

[dev.mysql.com...]

[BlackRaven]

also as a newbie this site helped me
[databasejournal.com...]

[mcibor]

It seems to be working:

SELECT name FROM table1 t1, table2 t2 WHERE t1.name = t2.user;

If in table1 you have a column name with eg.
root,
mike,
ted

And in table2 you have a column user with eg.
root,
root,
mike,
mike

This question will return: root, root, mike, mike.

Hope you can use it somehow.
It's important that you select only columns which have different names. So if
table1 is {id, name, pass, question}, and
table2 is {id, user, pass, question1}

you may only select: name, user, question and question1.
On selecting id and pass there will be an error.

May the New Year bring all the answers!
Best regards
Michal Cibor

PS. The code above is working in mySQL

[Nutter]

Just in case anyone else has this same problem, here's the solution I came up with. It may not be the best, fastest, or most elegant; but it does seem to work.

I have the WHERE clause broken down into two separate variables. The first, $primaryquery, is what is being searched on. For example,

$primaryquery = "(namelast LIKE '%".$_GET['namelast']."%')"

. Notice that it is in parenthesis inside the quotes.

The permission part of it is done with an array.

$temparray = $row['permissionfield'];
for ($i=0; $i<count($temparray); $i++)
 {
 $temparray[$i] = permissionfield LIKE '%".$temparray[$i]."%'";
 }
$secondquery = "(".implode(" OR ", $array).")";

The full WHERE clause becomes " WHERE (".$primaryquery." AND ".$secondquery.")". Then, I just taked on the SELECT, ORDER BY, and LIMIT clauses; and it worked.

- Ryan