Select Syntax - Is "something = ('this' Or 'that' Or 'other')" Ok?

[lb3000]

I am using MySQL v5.5.27. I usually would write this SELECT statement like this:

 

SELECT DISTINCT * FROM table WHERE something = 'this OR something = 'that' OR something = 'other' AND thingamajig = 'one' OR thingamajig = 'two'

 

But would this SELECT statement provide the exact same result?

 

SELECT DISTINCT * FROM table WHERE something = ('this OR 'that' OR 'other') AND thingamajig = ('one' OR 'two')

 

I have tried running this and it *seems* to be working. Just want to make sure this second way of doing things won't return errant data in some way that I can't think of.

 

Thanks for any insight, assistance!

[Jessica]

I'm fairly certain neither of those would even work at all.

 

You should use the parens to organize your precedence.

[lb3000]

Hi Jessica - thanks for your quick reply. I just noticed I have a missing apostrophe in my example - should be:

 

 

SELECT DISTINCT * FROM table WHERE something = ('this' OR 'that' OR 'other') AND thingamajig = ('one' OR 'two')

 

I have tried running this query and it does return what I would expect. No errors are returned. But like I said, I am just running the one query against a small set of data. I want to ensure that I'm not getting what I expect because I expect it - if that makes any sense.

 

If it's not too much trouble, can you explain a little more, or point me to more info, about what you suggest re "use the parens to organize your precedence".

 

Thanks a bunch!

[Jessica]

I'm 99% sure MySQL does not let you do where column = ('value' OR 'value').

You would do column = 'value' OR column = 'value'. Or using IN(). What is happening is MySQL is only going to do (according to your latest post):

SELECT DISTINCT * FROM table WHERE something = 'this' AND thingamajig = 'one'

And the rest of it is ignored. You're saying "SELECT * FROM table WHERE bob

Bob what? It doesn't mean anything.

 

 

In that case, you'd have to do:

SELECT DISTINCT * FROM table WHERE something IN('this', 'that', 'the other') AND thingamajig IN ('one', 'two')

OR

SELECT DISTINCT * 
FROM table 
WHERE 
(something = 'this' OR
something = 'that' OR
something = 'the other') 
AND 
(thingamajig = 'one' OR 
thingamajig = 'two')

 

One is much easier to read.

 

You may also want to just join to another table if possible.

Edited September 26, 2012 by Jessica

[lb3000]

Ok - that makes sense. I'll drop that idea and go back to the way I've always written a query like this, with everything spelled out:

 

 

SELECT DISTINCT * FROM table WHERE something = 'this OR something = 'that' OR something = 'other' AND thingamajig = 'one' OR thingamajig = 'two'

 

 

 

I just had a quick look around for operator precedence and see what you are getting at there. For my purposes the query written like the one above does return what I need - I'm confident of that. This query works with data that comes from a form with a mix of checkbox data (which provides this/that/other data in an array) and select pull-down menu data (just returns one value). Our search form doesn't let people do much more than check off or select the data they want returned. They don't have any preferences to indicate except for "any/all" on the checkbox data. (If they choose "any" we run the query with OR in between each value; choose "and" - we run query with AND in between each value).

 

Thanks again for replying, and for providing clarification. Much appreciated.

[Jessica]

So, you picked the longer, harder to read one....

[lb3000]

Yes - I am going to use your query:

 

SELECT DISTINCT *

FROM table

WHERE

(something = 'this' OR

something = 'that' OR

something = 'the other')

AND

(thingamajig = 'one' OR thingamajig = 'two')

 

Here's the thing - I would LOVE to use IN. But we do need to capture peoples preference that their search return a match on ANY versus a match on ALL. If they choose ANY, I need to deploy a query like (something = 'this' OR something = 'that' OR something = 'other'). If they choose ALL, I need to deploy a query like (something = 'this' AND something = 'that' AND something = 'other').

 

If I use IN, it would be as though I am running an "ANY" query all the time. Unless I'm thinking about that wrong.

[Jessica]

How can something = 'that' AND something = 'other'???

[lb3000]

Oh - sorry - right. A little background on this project. We collect publications from all over the world. When someone adds a publication they can index it in a number of ways. Substitute "audience" for "something". So someone might index their publication as serving "advocates", "policy professionals", "legislators". When someone else uses our search form -- which is what all of the above posts refer to -- they can choose to get a result set that focuses on publications for audience type: advocates AND legislators. Or they might want to be less restrictive and choose to get back a match on either advocates OR legislators. Here's a more accurate query, based on a "match ANY" request and then a "match ALL" request complete with joins:

 

SELECT DISTINCT

publications.title,

publications.date,

publications.description

FROM publications

JOIN link_audience ON link_audience.pub_id = publications.pub_id

JOIN audience ON audience.id = link_audience.audience.id

JOIN link_doctype ON link_doctype.pub_id = publications.pub_id

JOIN doctype ON doctype.id = link_doctype.doctype.id

WHERE

(audience.type = 'advocates' OR audience.type = 'legislators)

AND

(doctype.type = 'casestudy' AND doctype.type = 'whitepaper')

ORDER BY publications.title

Edited September 26, 2012 by lb3000

[Jessica]

My point is if you change that query to the below, which is what you're suggesting, it will not return anything.

SELECT DISTINCT
publications.title,
publications.date,
publications.description
FROM publications
JOIN link_audience ON link_audience.pub_id = publications.pub_id 
JOIN audience ON audience.id = link_audience.audience.id 
WHERE 
(audience.type = 'advocates' AND audience.type = 'legislators') 
ORDER BY publications.title 

 

One single column cannot be a value AND another value.

You would have to do multiple joins, and have multiple relationships.

Edited September 26, 2012 by Jessica

[lb3000]

Hmmmm but it does return something. It returns a title that has a row in the link_audience table that matches the id/type in question.

 

Audience table

 

id | type

=======

1 | advocates

2 | legislators

3 | researchers

 

 

Link table

 

pub_id | audience_id

================

1 | 1

1 | 2

2 | 1

2 | 3

 

So if someone requests a title that has been indexed 1 and 2 from the audience table, then they get back title id 1. If they ask for something indexed 1 or 2, they would get back title id 1 and 2.

 

I'm no guru! I could be wrong. But when i run a query I do get back what I expect. Which brings me back to my original post re: expectations.

[Jessica]

I misunderstood your table structure. Looks fine to me, sorry about the confusion.

[lb3000]

Jessica - thank you so much for all of your insight, and challenges. Seriously. I work over here all day long (and sometimes nights and weekends) alone, making this stuff up as I go along. I really appreciate that you took this on and kept coming back at me. Makes me want to work in an office or something, with colleagues who know what they're doing!

 

Thanks again! And glad to know that my entire query isn't a fraud.

[Jessica]

Ha! You're welcome.