During the creation of tables using mysql on phpmyadmin, I always find an issue when it comes to primary keys and their auto-increments. When I insert lines into my table. The auto_increment works perfectly adding a value of 1 to each primary key on each new line. But when I delete a line for example a line w…
Tag: sql
JOIN ON either of the two columns but not both
I have two tables (orders, agents) i’m trying to join on either of the two columns but not both. There are some records in orders that have both of these columns populated and it returns as duplicate orders: agents: I tried joining with an OR clause I’m getting the following results Expected Resul…
SQL- Find the price of the smallest product in a store
I would like to find the price of the smallest product in a store and in addition, in another column, populate this price in all the products of the same store. Here is my table and the desired result in the “results” column: Table1 Here is my request but it does not populate the price: SELECT loc…
How to retrieve a list of results that are NOT in another list with SQL
I have a normalized SQL database. The relevant schema looks like this: I need to get a list of users that have not been sent an email today for a specific category. Based on my understanding, I need to perform a LEFT JOIN. In an attempt to do this, I’ve tried the following: I’m using subqueries in…
MySQL:Trying to run CUBE query -> Error in your sql syntax | LIMIT 0,25
I have query in my structure trying run in PHPMYADMIN. I am trying to run CUBE query for OLAP operation, this is my query : I have also tried this query : but it showing this error : I tried to open the page to check syntax but it shows page not found. Answer I don’t think MySQL supports CUBE
subtraction of 2 select statements
I have a simple table: Neighbourhood Some countries have the same neighborhood name. All I want is the total minus the distinct. but I have a problem with the operator; Here is what I am doing. Answer So you want: I might suggest a further breakdown: This shows the frequency of duplication along with example …
is a LOCK TABLE needed for `UPDATE tbl SET col = col + 1`?
lets say that col is 0, and 100 clients is trying to increase it by 1 at the exact same time, is the LOCK TABLE needed here? or can i just do and be sure that col becomes 100? (eg that mariadb makes sure none of them reads the same value twice~) i’m basically trying to do the equivalent of
How to understand the execution sequence of SELECT and ORDER BY
I googled the question and all answers said SELECT was executed before ORDER BY. But the simple example below (using MySQL and sakila database) gives the correct sorted results. Obviously, ORDER BY is not executed after SELECT, as customer_id is not selected by SELECT. Can anybody explain what happened? SELEC…
SQL multi save without using loop/ cursor
I have a requirement where from a string of int[] I need to insert all the values of this array into DB. I have tried this solution and it works fine. To achieve this I have converted the int[] to XML in C#, and in SP used this XML variable to fill a table variable and looped over this table
A correct query produces a syntax error when used as a sub-query
First, I have this query, which works just fine: But then, I use it as a sub-query: And get this error: How do I fix this and achieve the intended result? How, in general, can a correct query become an incorrect sub-query? Answer Use a CTE: