I’m trying to count active users in the last month by comparing the logged event against today’s date. So for example if a user logged in 2 days ago(09/01/2021), the query would evaluate to true since the logged in month 9 = todays month 9 AND logged year 2021 = todays year 2021. since its only us…
Tag: mysql
Mysql duplicate results of query relation with 2 other table , IN operator
I have 3 main table : products, stores and categories product relate many-to-many with stores via table product_store product relate many-to-many with categories via table product_category How can i query all products, belongs a store ( with store_id : 1 ) and belongs one or many category ( with category_id i…
MYSQL joining two table slow
I have 2 main table in MYSQL DB. I will call it entityA and entityB. There are some logic that user will start to create a entityA and then convert it to entityB but some time they will not create entityA and only create entityB Therefore, I have created a middle table which stored entityAId and entityBId. en…
Single table or multiples tables (multiple company database)
I have to create a database for multiple companies, I have 2 options A single products table, with a company_id field to differentiate One table per company products_company1 products_company2 products_company3 Important: Data stored of each client will be updated every 1 hour Each company has 2000 – 30…
Most elegant way to search for a json value and replace it in mysql? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 1 year ago. Improve this question I have a table called Entity which stores a bunch of jsons. The Json is bas…
phpmyadmin how can i create a event like this?
I am creating an event on one of my databases to check if the client changed a specific parameter called ‘serverlog’ which is a boolean on my Sql database. Is there a way to loop through all the users in a table called ‘users’, check if ‘serverlog’ is false and update/set a…
Subquery to get derived value
I have the below table T in MySQL, with columns A and B. I want to query this table to get a value ‘C’, that has the count of the number of times the value in Column ‘A’ appears in Column ‘B’. For example, 1 appears 2 times in Column B, so the first row of column C should b…
Why are my counts different when using CASE operator VS IF function?
I learnt that the difference between CASE and IF statement is as follows “IF is a CASE with only one ‘WHEN’ statement”. But I got confused when querying this: I have the table Olympics which contains the countries and medals associated to it. I am counting the total number of medals wo…
SQL get most recent joined record
I’ve the following tables: chat: chatId, jobId chat_message: userId, chatId, message, created_at chat_user: chatId, userId Am trying to get the last message sent by a specific user for all chats he’s a part of. This is what I have so far (left join for debugging on null): The problem with this que…
how get records of a table as columns of other table for report
I have 4 tables: users id name 1 a 2 b products id name 1 aC 2 bC bought id user_id product_id amount 1 1 1 100 2 2 1 200 sold id user_id product_id amount 1 1 1 100 2 2 2 200 Report Now I want to have a query to get below report: user_id user_name bought_aC bought_bC