Complex where clauses, can they be nested?

Is there another way to do it or is this typical?

[Trisha]

The only way I've been able to get just the information I need out of a table I've been working with is with somewhat complex where clauses. At least they seem complex to me. I can do what I need to get done, it just seems like maybe there is a better way to do it than I currently am.

I end up with stuff like:
where color = 'Gold' and texture = 'smooth' or color = 'Yellow' and texture = 'smooth'

I tried something like
where color = 'Gold' or color = 'Yellow' and texture = 'smooth'

but it didn't work, so I had to repeat the texture part.
It seems like you should be able to do something like:
where (color = 'Gold' or color = 'Yellow') and texture = 'smooth'

But apparently you can't, since it didn't work when I tried it.

Pretty soon I'm going to be trying to do stuff like:
where maincat!= gadgets and category = widgets and color = blue or color = navy and texture = smooth, etc. - even more complex.

I suppose if the database was set up differently it would help, but I can't control that. If I don't make it more complex I'll end up pulling out stuff I don't want or missing something I need.

It seems like there should be a simpler way. Or is it typical for where clauses to be complex like this?

[brucec]

Hi Trisha,
I also responded to your post about the PHP and MySQL about the 5 sections of your article.

Low and Behold, there are two ways that I can think of to accomplsih this. I hope one of my suggestions can answer this for you and make it a "less complex" way of doing this in MySQL. :)

A good way is to use the IN keyword after your WHERE clause. It's pretty cool.

What you have to do is think of it like a set. You can say is color IN ('Gold', 'Yellow', 'Red', 'Green')

That's all there is to it. So, instead of the equals sign, you will use the IN keyword.

Of course you cannot combine fields and you will need an AND operator for the texture field, but I think this solves it.

Like SELECT * from table_name WHERE color IN ('yellow','gold') AND texture='smooth'.

Of course, the more fields you use, the more complex it will get, but this should cut down the complexity some.

I hope this helps. My second way is in the next post.

[brucec]

The second way is to use the UNION keyword. It allows you to break your complex SELECT statements down into smaller SELECT statements.

Like in your PHP code, you can use the concatenation operator .= and break down the SELECT statements and then use UNION between them. Here's an example:

$SelectStatement = "SELECT * FROM whatever WHERE color='yellow'";

$SelectStatement .= "UNION SELECT * FROM whatever WHERE color='gold'";

$SelectStatement .= "UNION SELECT * FROM whatever WHERE texture='smooth'";

That could work as well.

Let me know if one of my methods (of madness, not doubt) works for you.

Bruce C

[Netizen]

You should look at bracketing the relevant terms in the where clause to allow MySQL to work out the precedence

e.g

WHERE (color = 'Gold' OR color = 'Yellow')
AND texture = 'smooth'

I don't know why this didn't work for you - what happened?

Or, you can do

WHERE color IN ('Gold','Yellow') and texture = 'smooth'

as brucec said

[Trisha]

I'm going to have to follow up with this sometime in the future. My dad has some health problems and I won't be able to work on this for a while. Thanks for all the advice though.

[brucec]

Good luck, Trisha, with your dad.

[Trisha]

I finally had a chance to work on this again today, the IN ('Gold','Yellow') thing worked! Thanks for the help! (I bookmarked the thread too though, I may try one of the other methods another time.)