In my web application, I have 3 tables: TAB A: Recipes (id, name) TAB B: Recipes_Ingredients(id, fk_recipe,fk_ingredient, quantity) Bridge table TAB C: Ingredients (id, name) I need to find all the recipes(TAB A) that containing a provided list of ingredients (TAB C). Es: I provide a list of ingredients id li…
Tag: mysql
SQL query for duplicate rows based on 2 columns
I have 3 tables movie, rating and reviewer movie has 4 columns movieID, title, year, director rating has 4 columns reviewerID, movieID, stars, ratingDate reviewer has 2 columns reviewerID, name How do I query reviewer who rated the same movie more than 1 time and gave it higher rating on the second review. Th…
updating data in a table from another table
I have two tables: cart_item product How to correctly update the data in the product table based on the data in the cart_item table? I want to update the quantity of products in the table product. By updating the data in product I will delete all products in the table cart_item. Here is what I would like to g…
Why does this Sql query shows the possible changes but does not implement it?
So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it. SELECT Concat(‘RENAME TABLE ‘, TABLE_NAME, ‘ TO fan_’, SUBSTRING_INDEX(TABLE_NAME, ‘pc_’,-1), ‘…
Trying to utilize a window function instead of this script
I’m trying to improve my query for this topic at hand. I’m trying to find the top 5 and bottom 5 growth rates per state from 2020 to 2021 in my org. The table has the columns as specified: orderid, orderdate, totaldue, state, etc. (these are probably the most important columns). This is the query …
Can anyone help me on how to fetch MySQL table/s data in JSON format by querying itself which is similar to MS SQL Server
Below is the existing T-SQL code which generates table data in JSON format just by using the keyword FOR JSON PATH, include_null_value. How to implement the same in MySQL query itself??? Need possible solutions. /** Sample Table data in MS SQL server ***/ /** Fetching table data in JSON format ***/ key value …
MYSQL Error #1415 on Function Creation: Not allowed to return a result set from a function
Trying to create a conditional-based function that will return a result. I don’t if it’s the way I am setting the result value that is causing the error? Making MySQL throw the error code 1415 Not allowed to return a result set from a function. Answer your code has multiple problems, but the bgges…
How do I see the COUNT results for only the top values in the table and not the entire table in sql
I am creating a Database for my utility bills in SSMS18, and I am trying to retrieve the count for the occurrence of a specific month in the top 10 months with the highest bills. How do I restrict the COUNT feature to only count the top number of rows? Table design I can limit the results by adding a
How to select multiple rows of the same user that satisfies some condition in SQL?
A have the following data I want to select all users who have visited both Spain and France. How can I do that in MySQL? Answer Something like the following should suffice:
improve sql query with 2 EXISTS sub queries
I have this query (mysql): It runs multiple times on app start. It takes more than 10 seconds (all of them). I have indexes on: balance_histories table: budget_item_id, family_id (tried also payment_date) budget_lines table: family_id, budget_id, budget_item_id How can I improve the speed? Query or maybe mysq…