[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Mysql WHERE statement?

SELECT * FROM members
  WHERE ListLast(bus_catg,'-') = '#qSUB.id#'
  OR ListLast(bus_catg1,'-') = '#qSUB.id#'
  OR ListLast(bus_catg2,'-') = '#qSUB.id#'

I'm trying to get my WHERE statement to only select records where the bus_catg number matches.

The data in a bus_catg field would be like

10-115

I want the query to only match records which have 115 in them.

So the query would look like this with actual data

SELECT * FROM members
  WHERE ListLast(10-115,'-') = '115'

I'm getting an error that I don't know the MySQL to overcome it.
0
day6
Asked:
day6
  • 9
  • 3
  • 3
  • +1
1 Solution
 
Roger BaklundCommented:
ListLast() is not a mysql function, but you can use

  substring_index(bus_catg,'-',-1)

If this does not work, post the error you are getting.
0
 
Cornelia YoderArtistCommented:


SELECT * FROM members
  WHERE bus_catg like '%115'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that query could be changed to looking like this:
SELECT * FROM members
  WHERE bus_catg1 LIKE '%-115'

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Cornelia YoderArtistCommented:
day6, angelIII's inclusion of the dash is a good idea.  Be sure to share the points among near-simultaneous correct answers :)
0
 
Roger BaklundCommented:
yodercm, are you insinuating that my answer was incorrect? ;)
0
 
Cornelia YoderArtistCommented:
cxr, not at all, but I think mine and angel's is easier to understand and apply.  I just didn't want angel to be gyped of points because his post came in behind mine, saying the same thing.
0
 
day6Author Commented:
Your answer was the correct one because without the dash, it gave inconsistent results.  I had actually already figured this one out before I received any answers from this post and my own testing revealed the dash had to be included in order to get accurate results.
0
 
Cornelia YoderArtistCommented:
Oh, great, I give the right answer first, take the trouble to mention that angelIII should SHARE the credit, and so I get none of it???  Thanks a lot, day6.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yodercm: as you cannot see the grading comment, I will share it:

Grading Comments:
Your answer was the correct one because without the dash, it gave inconsistent results. I had actually already figured this one out before I received any answers from this post and my own testing revealed the dash had to be included in order to get accurate results.
0
 
Cornelia YoderArtistCommented:
angel: except of course, yours also wasn't correct, because you misspelled the table name.  :)
0
 
Cornelia YoderArtistCommented:
er, that is, the field name you misspelled. :))
0
 
Roger BaklundCommented:
yodercm, you did NOT give the right answer first. I did. Yours was incorrect. However, in my opinion it is the askers privilege to choose the answer that works for him, and I have no problem with accepting his decission in this case. There will often be multiple ways to solve a problem, we can't expect the asker to investigate all possible routes to a solution.
0
 
day6Author Commented:
yodercm...

first of all... aren't you a bit whiny about points?  It's not like winning the lottery... sheesh.

Secondly... your answer was "not" correct and would not generate the desired results.  Had I not already experimented with the LIKE statement on my own, I wouldn't have determined that the dash was needed.  In fact, I could have easily came back to this post within 20 minutes of it posting with the fact I had already solved the issue, but I figured I'd see if anyone else had a quicker or better way to get the result.  When I saw his answer with the dash, I figured I'd reward him since he definitely debugged the code of a percentage sign with just the number.  The problem was that without the dash it yield answers like 15 instead of 115.  That was the issue.

Keep trying, I have plenty of issues to work through from time to time.
0
 
Cornelia YoderArtistCommented:
angel:  Your answer was NOT correct, and mine also gave the CORRECT answer to the problem, namely, using LIKE.

day6:  No, I'm not whining about points, I'm on a crusade to get people to SHARE points among simultaneous correct answers.  I don't give a crap about points, I give a crap about fairness.  Not to worry, I won't bother you again.
0
 
day6Author Commented:
For the record... YODERCM... fairness is awarding points to someone who gives the RIGHT answer... not a partial answer... close answer... answer with a partial concept... whatever you want to twist to describe that your answer was CORRECT.  It was NOT correct and therefore you did not get a portion of the points or any points period.

I'd recommend that if you want fairness, stop being such a whiner when points are awarded to someone who gave the CORRECT answer.  You get shared points if your answer "also" worked to my satisfaction... which in this case it didn't.

As for bothering me... you didn't and don't.  Just give CORRECT answers and you'll get all or a portion of the points in the future.  I welcome your attempts as well as anyone who cares to try.
0
 
Cornelia YoderArtistCommented:
As I said, I won't be bothering YOU again.
0
 
Cornelia YoderArtistCommented:
@angel:  Professionalism?  I campaign for fairness in splitting points among simultaneous correct answers, as you well know.

When I am the FIRST poster of a correct answer and someone else posts immediately afterward, I add the suggestion I added here, to split the points, to try and ensure that the second poster gets a fair reward for his answer even though I happened to be first.

When I am the SECOND poster in such a situation, I do NOT post that suggestion, but later, if the points are not split, I post it afterward as a suggestion for the future.  I can't see how I could be any more professional than that.

@day6:  To suggest that I am whining about points shows your ignorance of EE and an attitude that I would not care to deal with ever again.  I have personally blacklisted you, not for anything to do with points, but for your arrogant rude attitude toward people who volunteer their time and expertise to help you.

What do you think we get for your 500 points?  We get a T-shirt for every 300,000 or so points we earn.  I already have more T-shirts than I can wear, and two more are in the mail to me right now.   I don't need points, and neither does angel, but we do want respect and gratitude for our effort to help, whether or not you think it was the perfect answer.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now