0

I have a piece of code that should return a count of 4, but only comes back with a count of 1. This is my query.

SELECT *, (SELECT COUNT(*) FROM `users` WHERE `user_id` IN (`enc_vs`) AND `rpg_curr_hp` > 0) AS playerCount FROM `encounter` WHERE `enc_id` = 32

The row with enc_id of 32 in encounter has a group of user id's which are 1,62,12,23 and that is enc_vs. When I do the count with numbers, it works and returns a count of 4. But when I use enc_vs instead, it comes back as 1.

This would result in:

enc_id |   enc_vs   | playerCount
   32    1,62,12,23        1

The users table looks something like

user_id | rpg_curr_hp
   1         55
   2         50
   3         55
[all the way down to]
  12         57
  62         55
etc...

The enc_vs is a string of id's for the users table. I need it to result in

enc_id |   enc_vs   | playerCount
   32    1,62,12,23        4

assuming that all 4 players have a rpg_curr_hp greater than zero.

EDIT: THIS IS REGARDING A COUNT, NOT A SEARCH FOR COLUMNS.

Fyro
  • 1
  • 1
  • provide some data samples – Alex Jan 15 '18 at 22:12
  • 1
    not sure if my guess is correct but try to replace `IN` with `FIND_IN_SET` function – Alex Jan 15 '18 at 22:17
  • Yeah, Alex is right. Sounds like you've got a comma-delimited string and that won't work like you expected. – shawnt00 Jan 15 '18 at 22:21
  • 2
    if you have a comma delimited column...refactor – Ctznkane525 Jan 15 '18 at 22:22
  • I'll provide some samples, just not immediately.. doing multiple things right now. The value for `enc_vs` for the record in question is '1,62,12,23'. But this results in 1. But if I replace `enc_vs` with with 1,62,12,23, the count will result in 4. Which is what I'm trying to achieve. – Fyro Jan 15 '18 at 22:31
  • It is absolutely unclear what you are talking about. Please provide clear data samples and expected result, as well as current (wrong one) you have – Alex Jan 15 '18 at 22:35
  • Updated my post in an attempt to explain a little clearer. I appreciate your patience since explaining things isn't really my strongest aspect of myself. :( – Fyro Jan 15 '18 at 22:48
  • you provided a sample just for one table. take this fiddle, fill it with data and put link into your post when it is ready http://sqlfiddle.com/#!9/fa6e0f – Alex Jan 15 '18 at 22:54
  • The encounter table is pretty much what the results are, I just need to include a column counting how many players have a health greater than zero. – Fyro Jan 15 '18 at 22:57
  • http://sqlfiddle.com/#!9/17ba5e/1 – Alex Jan 15 '18 at 23:25
  • Unclear? @Alex, you already gave the answer! – shawnt00 Jan 16 '18 at 00:08
  • It pointed me to the right direction, thank you! – Fyro Jan 16 '18 at 06:01

0 Answers0