fenway

Well, the IN() is the way to go -- use COUNT(*) to check.

No, the OP is still referring to the derived table aliases that dont' exist.

One UID, one update.

Sorry, I don't follow.

Well, you're missing an ON clause.

Ah, "correctness" -- that's like trying to claim that certain sorting algorithm are poor choices just because in "the general case" they might be slow. It's a futile effort -- that's computer science, not computer programming -- they're not the same. My algorithm doesn't need to be "proven correct" by mathematical induction in order to execute a for() loop.

I don't think you mean AND -- otherwise, you're only getting back those rows which contain your search value in BOTH fields.

By partial I meant the first statement and not the second.

Sure, go ahead -- YOU improve the optimizer. That's like asking someone who speaks French to go learn English because I only speak English. In the meantime, the rest of us will continue to use the existing MySQL parser providing it with statements the way it prefers.

You're missing a GROUP BY nickname.

This topic has been moved to PHP Coding Help. http://www.phpfreaks.com/forums/index.php?topic=338643.0

You store multiple rows, not arrays.

Oh, missed that -- you can't table prefix a COUNT().

Sure. "SELECT * FROM items WHERE clothing IN ('Pants','Socks','Shoes')"

Then self-join.

What error?

I was going to lock this topic, because once again Jaynesh has posted duplicate topics, but since there are replies already, I'll leave it for now. @ebmigue: programmers don't need to learn calculus or algebra to use SQL. In fact, no one does outside of academia. That's like pretending that I can't turn on a computer without understanding the quantum mechanical aspects of a transistor.

Sure it will -- the OP is looking for matching rows, and since the WHERE clause doesn't refer to NULL values, the condition will be invariant. Granted, there's no need for LEFT JOIN is this case, though.

Why would it have duplicate data to begin with?

Theory has no place is the real world -- even Codd knew that, and amended his 2-value system. Besides, while you can get a way with '' (blank) for strings, you can't get away with "0" (zero) for integers. "No apples" isn't equivalent to "indeterminate number of applies". But pretending that indeterminate values (a) don't exist, (b) don't need to be stored, and © have no place in the real world is just short-sighted. Your "proofs" and "correctness" are ill-suited, where actual problems need to be solve, not addressed on a theoretical level and no further. But if you're an experienced programmer, you know all of this, so why bother fighting?

No, I don't understand.

Third-party search tools, like sphinx.

Again, same topic -- locked.

Same table.

"does not work" isn't useful -- what doesn't work about it?