I’m trying to get a MySQL query to combine two tables. Both tables have a user_id and date column and the other columns are unique to each table. What I’m looking to accomplish is best explained with an image (imgur). I’ve tried various joins and unions and can’t get the right combinat…
Tag: mysql
How to get next item in mysql order by createdAt
My frontend app can list of my items order by createdAt (for sure by calling API): Output is for example: Id of each item is UUID (there in example just myId*). I can open detail of item by url /item/detail/myIdPPP and on this detail I have GO TO NEXT item. The problem is, that I don`t know how to create
How to neglect timestamps when inserting date to MySQL database
I want to insert a date into MySQL table. But, even if I use strftime(), the timestamps are showing like 00:00:00. How can I get rid of it? Output: Answer Obviously the column Date has data type DATETIME or TIMESTAMP. If you don’t need the time part of the column, you should change the data type to DATE…
Update & Select a MySQL table in FIFO queue with non-unique identifiers
I have this table documents based on client’s set of data. Only provided key is documentId, but it’s non-unique (and rowId is mine, not client’s). This table also shows which documents have been processed and which have not, plus some additional data. isProcessed is a generated column (isPro…
MYSQL inner join counting only once depending on two conditions
I have a SQL query where I’m joining three tables that have information about employees, their departments, their schedules, if they are in vacations or not and reasons why they skipped work on an specific date. In one of the tables I have as foreign keys the employeeId, the scheduleId and the date in w…
Error Code: 1826. Duplicate foreign key constraint name ‘menu_ibfk_1’
I am writing a code on MySQL and it’s giving me errors. This is my code and it’s giving me the error – “Error Code: 1826. Duplicate foreign key constraint name ‘menu_ibfk_1’ ” When I give a different constraint name, it give me the error – “Error Code: 182…
Find the first AND last date that an ID occured in dataset SQL
I have a very large data set with over 1 million rows and over 70 columns. The dataset contains data about back orders of an organization. Every day a snapshot is being made and all orders that are backorders are added to the dataset. I want to know what the first date is when an OrderID occurs in the dataset…
How to use IF statement in a calculation in a SQL query
I use following query to give me the earned commission of our sales agents. Now I want the output to be sorted by (sales * sph * factor). Where factor is 10 if sph > 1.5 and else 7.5. Is it possible to implement this in the query. If yes how do I achieve this? It would also be good
sql query results depend on attributes of other table
Cars: CarAttributes: How would the sql query look if the returns depend on the attributes of the car like following: Answer You can use aggregation with a having clause:
Improving the performance of a MySQL left join sub query
I have the following MySQL query which calculates the total number of orders for each month within a given date range e.g. a year. The query works correctly, but the performance is slow (around 250ms). Any ideas on how to rewrite it to make it more efficient? UPDATE: Some have suggested using two left joins, …