0

Using MySQL:

SELECT * FROM Transactions WHERE userID = 4 and month(date)>11 and cat2 regexp '%gas stations%|%food%|%restaurants%'

I'm trying to get the transactions for user 4, in the month of December, that have the three values listed for category 2 (cat2). I am getting those transactions that I want but am also getting transactions in all other months that have the three corresponding category 2s. How can I filter all of those out?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jepf
  • 87
  • 2
  • 10
  • 3
    Why are you storing multiple categories in a single column? You should have a table with one row per item and per category. – Gordon Linoff Dec 15 '16 at 03:15
  • 1
    `month(date)>11` some of us would just use `month(date)=12` unless you use some other calander –  Dec 15 '16 at 03:16
  • 2
    You do not need `%` when you are using `regexp` – EhsanT Dec 15 '16 at 03:33
  • I'm surprised this gives any results. Can you show a sample of your DB? Are you using `%`s as delimiters? You aren't using the `like` operator here.. – chris85 Dec 15 '16 at 03:54
  • http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 _I store my data the totally wrong way, and now I'm having problems using it. It's complicated/difficult/too slow/doesn't work right! Can someone help?_ The answer is Yes - fix your data so it's stored properly to begin with, and all those problems using it go away. You don't have to optimize difficult things when they're not difficult in the first place – e4c5 Dec 15 '16 at 08:30
  • The transaction data is returned from an API with cat1, cat2, and cat3 identifiers, which have 10's of options each, per each transaction. Are you all saying a database with 100+ columns would be the way to go to catalogue each transaction? – Jepf Dec 15 '16 at 20:30
  • Please be sure to put large sections of code in code blocks, not code spans. – RamenChef Dec 19 '16 at 03:30
  • if you need December why `month(date)>11` instead of `month(date)=12` ? – Javier Larroulet Dec 06 '18 at 13:48

0 Answers0