Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

multiple OR operators in WHERE statement

 
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good evening everyone:


For the class that I'm taking, I need to identify if one region of the country has more instances than the others.
The regions are provided in a separate document that breaks the states down into groups of 5+ each.

I'm pretty sure that I could include every state for each region in the WHERE clause, listing each state individually.

I'm trying to figure out how to avoid having 10 or 15 different OR operators in the same sql statement.

I'm using MySQL 5.5.62 running on a VM with ubuntu0.14.04.1.

I'm very much a newbie, and I'm not very proficient at scripts.

Thanks,

--Bill
 
Marshal
Posts: 25795
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it was me, I would make a table containing region and state, so that I could join that table with the state table that I assume you have. Does your assignment allow you to do that?
 
Bill Platt
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Does your assignment allow you to do that?



There isn't anything specified that says that I can't do it.

Unfortunately, the data set is a table that was provided contains almost 38K entries for customers, and a table that contains the orders, so creating a table would be time-consuming.


Each state contains in excess of 500 entries, it's why I wanted to use the OR clause.

thanks for the response

--Bill
 
Bartender
Posts: 1197
22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Bill Platt wrote:

Paul Clapham wrote:Does your assignment allow you to do that?



There isn't anything specified that says that I can't do it.

Unfortunately, the data set is a table that was provided contains almost 38K entries for customers, and a table that contains the orders, so creating a table would be time-consuming.


Each state contains in excess of 500 entries, it's why I wanted to use the OR clause.

thanks for the response

--Bill



If I'm not mistaken, Paul was suggesting a simple 50-row-max table that showed the region for each state:


Now by JOINing and GROUP-BYing you should be able to get data on a region-by-region basis.

 
Paul Clapham
Marshal
Posts: 25795
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Precisely.
 
Saloon Keeper
Posts: 22479
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL has a very nice operator that can be used in place of multiple "OR" tests when a single column is compared. It's the "IN" operator. Like so:

However, from the sound of it, you're really looking to collate statistics by regions and that would require multiple SQL queries. So the use of a region table joined with the primary table, as others have described here, would be simpler.
 
Bill Platt
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, everyone.

I contacted my instructor about the permissibility of creating another table, he says that "it's outside the scope of the assignment".
He also said that he is looking for the use of multiple OR multiple times in the WHERE clause.

I have completed the assignment, I used OR multiple times in the WHERE clause for each region.

--Bill
 
Paul Clapham
Marshal
Posts: 25795
69
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suppose your instructor hopes to teach you how to use the OR operator in SQL, but in real life it would be horrifying if you actually had to do what you just did. I guess it's a good thing I didn't go into teaching...
 
Bill Platt
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
**UPDATE**

I ended up creating a Regions table and joining the two tables to get the results that I wanted.

It was a bit of a PITA to populate the table, as there wasn't a csv file available to load in. The regions that I was given was just on a piece of paper as part of the assignment prompt.

I managed to pull off an A on the assignment.

Thanks for all the help

--Bill
 
Tim Holloway
Saloon Keeper
Posts: 22479
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Bill Platt wrote:**UPDATE**

I managed to pull off an A on the assignment.



 
I carry this gun in case a vending machine doesn't give me my fritos. This gun and this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic