CREATE TABLE Friends_Relations( buddy_id VARCHAR(255) NOT NULL, mate_id VARCHAR(255) NOT NULL, PRIMARY KEY (buddy_id, mate_id), FOREIGN KEY (buddy_id) REFERENCES Users(id) ON DELETE …
Tag: mysql
Compare two MYSQL tables and return missing dates and group by column
I have two tables. I want to compare table A with B and get the missing date from with name of the user. TABLE A TABLE B EXPECTED OUTPUT Also, Along with it can I get the count of the missing dates for each user if possible? Answer You must cross join Table B to the distinct names of Table
Rolling sum for a column in mysql
I would like to get the rolling sum for the growth column for the following sample data which can be found here dbfiddle The output should look like, Growth RollingSum TMonth TYear 511 511 …
Mysql analyze table content
I forgotten the name of the SQL command to analyse the content of a table. The command I’m looking for shows me for each colum the min/max/avg length of the content. If I remember correctly it starts …
Can someone suggest a way to do this query using Doctrine (QueryBuilder) with multiple “where” clause?
I’m new to the QueryBuilder and I’m trying to do a POST request (with a JSON) to retrieve some informations in my database. I’m using array because each property can have several values. Here’s the JSON I’m currently sending : Here’s my database : The request I would like t…
Sql query to select several items from a another table in relation to values on a single row of a table
These are my two tables, wish do a mysql query to return: id_order,qty,id_user as name1 and updatedby as name2. I’ve tried this query but not working: Answer Use this. See the demo here: DB-Fiddle
Count distinct per fiscal year and display all dates in query result
DB-Fiddle: CREATE TABLE customers ( id int auto_increment primary key, order_date DATE, customerID VARCHAR(255) ); INSERT INTO customers (order_date, customerID ) VALUES (“2020-01-…
Display value in column although it is excluded by a WHERE condition over another column
DB-Fiddle Expected Result: In the result above I want to list all itemID and orderID that have shipped_volume > 0. Therefore, I went with this query: It gives me almost the result I am looking for. The only issue I have is that it puts a 0 for all rows in column sum(order_volume) which is caused by the WHE…
Create a descending list of orders per item and display the ranking position in seperate column
DB-Fiddle Expected Result: In the results above I want to create a descending list of all orderIDs per itemID. Starting from the newest order to the oldest order which is defined by the event_date. The position of an orderID within a certain itemID should be displayed in column position. I tried to go with th…
Delete with inner join and trigger throw error: Can’t update table ‘table_b’ in stored function/trigger
Why the following error (Can’t update table ‘table_b’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger) appears after i try to delete with inner join? Can i solve it? Answer I don’t see the point for a trigger here. The funct…