vdk | building streaming services | Video Utils library

Take a look at the following query. How do you display Column 'Action' as text. If the result of 'Action' is LEQ 0 then dipslay the text "Crash" and if 'Action' is GRT 0 display the text "Hold"?

This a new question/update from a previous thread. MySQL + Query to return all rows that have cols that are 'active' in another table

I am using PHP and MySQL to return some data with a query thank to the help of member @Nick

I currently need help in tweaking the current query to somehow return substitute data from another table in the rows/data it is currently returning.

Here are some current examples of what the table set-up is like, and the current query state:

REXTESTER: http://rextester.com/RZXI72814

SQL FIDDLE: http://www.sqlfiddle.com/#!9/296b5/3

It currently works as I outlined in the previous request, but once I put it to use, I realized I missed a requirement, which in hindsight I feel is fairly significant...LOL (sorry)

Current State: (working as expected) Query that returns all rows from the 'barbot_drinks' table [that has (1-15) ingredients made up of (3 cols each) _dispenser# _code# & _dosage#].. that are EACH found in the barbot_ingredients table AND are active.

If any of the _dispenser# _code# & _dosage# cols are empty, the whole 'ingredient' is invalid...and empty fields dont count...usually signify the end of the drink recipe (a drink can only have a max of 15 ingredients)

example: if a drink recipe/entry only has 2 'ingredients' (ingredient_1_dispenser, ingredient_1_code, and ingredient_1_dosage & ingredient_2_dispenser, ingredient_2_code, and ingredient_2_dosage)

VODKA & OJ

it checks the ingredients table to see if both VODKA and OJ entries exist in the barbot_ingredients table AND are active.

So thats where we are now.

What I need to try and do is get the 'dispenser_order' value from the barbot_ingredients for -EACH- ingredient_x_code column (which could be 1-15 per drink)..for each drink that gets returned. (ie: all ingredients for that paticular drink are found in the ingredients table and are active)

I am parsing each row and creating a string using PHP from the result set from this query.

what I am currently creating:

Example (Screw Drive Drink): (after being parsed by php script)

bottle=vdk:1,valve=oj:2000

or (col names)

[ingredient_1_dispenser]=[ingredient_1_code]:[ingredient_1_dosage],[ingredient_2_dispenser]=[ingredient_2_code]:[ingredient_2_dosage]

what I need to build is this:

bottle=1:1,valve=1:2000

or

[ingredient_1_dispenser]=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_1_code###]:[ingredient_1_dosage],[ingredient_2_dispenser]=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_2_code###]:[ingredient_2_dosage]

ie: (after being parsed by php script)

bottle=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_1_code###]:1,

valve=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_2_code###]:2000

The current is long because of the 1-15 possible column combination, but I also asked for something was easily readable so I can also understand and learn from it.. (like I have so far!)..

Let me know if there is any other information I can provide, or if I made anything unclear. I wanted to give full background, links to working code, and examples of what I am getting/doing, and what what I -want- get/do.

Using the following MySQL 5.6 query SELECT sum(col1 + col2 + col3) / 3 AS Result FROM table1

How can I do the same thing only ignoring NULL values? Also, adjusting the divide by number subtracting the NULL values?

Our lab uses a sensory panel to asses certain quality points of our product. Each panel member scores -1, 0, or 1 for each sample. For any given sample we can have 1 to 15 scores that they want averaged as 'Result'.

Reference to Question #24398431

Below is the Query I just tested. I received no Errors but did not get any data at all returned. I will take all the help I can get.