MySQL where function like multiple conditions for single value

Applications Programming
1

Hello,

First of all i am not 100% sure if this question is in the right category but this was the only one that mentioned SQL.

I am a bit stuck at a problem with mysql where i hope to get some answers if this is possible. The query below is (part) of a query that we use to check if our stock ( prices_stock, locally ) against the stock that is online on one of our marketplaces ( bol_stock# ).

The problem what i have is how i best can check if it is different for the last 3 times ( bol_stock, bol_stock2 and bol_stock3 ), so in short prices_stock has to be different then bol_stock, bol_stock2 and bol_stock3 before it needs to be added.

I have the query below where i firstly limit the prices_stock (should not matter for the question itself) and then match it against the first stock but i cannot figure out how i can match it against all 3. Is this even possible? I tried googling for this but i only get results back that mention that you just have to add multiple where lines but that does not help.

SELECT
tbl_bolapi_nl_check.sku,
IF(tbl_product_prices.supplier_id = 1 OR tbl_product_prices.supplier_id = 19, IF(tbl_product_prices.stock > 25, 25, tbl_product_prices.stock) , IF(tbl_product_prices.stock > 5, 5, tbl_product_prices.stock)) AS prices_stock,
tbl_bolapi_nl_check.stock AS bol_stock,
tbl_bolapi_nl_check.stock_check2 AS bol_stock2,
tbl_bolapi_nl_check.stock_check3 AS bol_stock3,
tbl_bolapi_nl_check.stock_check4 AS bol_stock4
FROM
`tbl_bolapi_nl_check`
LEFT JOIN tbl_bolapi_nl ON tbl_bolapi_nl.sku = tbl_bolapi_nl_check.sku
LEFT JOIN tbl_product_prices ON tbl_product_prices.sku = tbl_bolapi_nl_check.sku
WHERE
IF(tbl_product_prices.supplier_id = 1 OR tbl_product_prices.supplier_id = 19, IF(tbl_product_prices.stock > 25, 25, tbl_product_prices.stock) , IF(tbl_product_prices.stock > 5, 5, tbl_product_prices.stock)) != tbl_bolapi_nl.stock
2

Normally I prefer simple SQL queries and generally move all complex, hard to debug, SQL query logic to the application code.

1 Like
3

We do not have a real application which runs this. Its just a bash script that we use to check the different parts of the api. This query we are using to check if the data that is being send to the api is also handled by the api. Since this api does not have any webhook design and we cannot trust the return info (last week they had a week long failure even though you would receive a code 200 ok) so we have to resort to this. I can get it to work in awk also if needed but i figured that it would be better to just handle it in the query itself and do all the checks in there.

Since i only know some basic and medium sql i was hoping there was an option to let it check all 3 at once to keep the code as clean as possible. It only has to run for about a year in which i hope they will actually will implement webhooks (they are working on it for the next version).

4

Understood @SDohmen .

Regardless of what code you use, my experience is that simple SQL queries with most of the logic outside of the SQL query is easier to debug, modify and maintain.

It is for that reason that I do not write complex, hard to debug SQL statements (and for the past two years, I have written countless SQL queries for a huge production application).

Also only FYI, I do not execute nor process SQL queries using Bash. I prefer Python or Ruby or even PHP over Bash for DB connection management, DB security and SQL processing.

On the other hand, I do use Bash for dumping and restoring DBs (making backups and performing recovery tasks) and other basic sys admin tasks.

My personal choice is to process DB queries would be in the following order:

  1. Ruby
  2. Python
  3. PHP

Since Python is much more popular than Ruby these days, Python would be a good choice since most people do not program in Ruby.

Finally, I have found that these complex JOIN queries put "unnecessary performance pressure" on the DB, so I tend to do the joins in code (not in SQL), as mentioned before. It's easy to join, filter and select arrays in Python, PHP and Ruby, etc and it's much easier to debug (writing print statements insides the loops) and look for errors outside SQL queries.

Anyway, I am a simple developer and prefer the "Keep It Simple Stupid" (KISS) approach and don't write complex SQL queries because of this. I avoid software and sys admin complexity unless there is no other way around it. My time is valuable and I prefer not to waste it trying to write a complex, single "elegant" query when I can do the same task faster with simple, multiple queries and do the heavy lifting in the application code (by application, I mean the script or programming language which executes the SQL query, like Python, Ruby, PHP, etc).

1 Like
5

Sorry for the late reply but i took your advice and moved most of the logic outside the query (and into bash) and just send the whole result with some minor adjustments to the email from where i can filter it down further to check only the real problems. The full list is about 100 results which after further filtering is only like 5 lines. Sometimes i can skip the filtering since the api gave only the filtered down version as its not the cached version at that time.

Since i am in the process of rebuilding all older bash scripts to python code i will surely think of your comments to keep it as simple as possible.

1 Like