Hello I can’t figure out how to write this select statement. I have the following schema: table 1 1:n table 2 n:1 table 3 So I got a n:m between table 1 and 3 what I want to do now is get all entries in table 1 that either don’t have an relation to table 3 via table 2 or
Tag: mysql
Query with offset returns overlapping data sets
Initial attempts at getting a very simple pagination, using fetch n rows and then a subsequent call with offset, gives overlapping entries in Oracle. I was expecting the following to give me two unique sets of results. 1-100 and then 101-200 of the results that would have been returned if the first line had b…
MYSQL/JSON: compare an array with the list of a column in JSON format and count the number of terms that match
I want to create a simple search engine that can count the number of properties that match on a user. I am using a field in JSON format that lists the properties, and when the user enters a search it …
what is the best way to join records of multiple dimension tables that are all connected by a common fact table
So I have a fact table that connects multiple dimension tables. Say for dimension tables I have customer, product, and date. Customer has id, name Product has id, price Date has id, year Fact table …
SELECT statement to remove empty columns from resultset with variable data
I’ve got a table that has been imported from a CSV as a midstep to gather data into other tables. All columns in this table may be empty but as long as one column has data, is a valid row. Because of this, there may be full rows with empty data, that are not valid to take into consideration. Here’…
Delete all duplicates except first one mysql
I have a table with a column serial_number that is repeated a few times. How would I delete the entire row except the first duplicate? By the following, I can select all the duplicates. But can’t delete. SELECT serial_number, COUNT(*) FROM trademark_merge GROUP BY serial_number HAVING COUNT(*) > 1 An…
How do I find the time periods (start and end time) by calculating the difference between two columns?
I have a table that: So, the time period for which the server was offline, the resultant desired table: I tried the following query: I am unable to logically write out the query, the above one certainly does not work. Answer You can use lead():
Incorrect datetime value in MySQL table
I have just created a new MySQL in an AWS Ubuntu instance. Then I have copied a table from another MySQL server to the new created database. This is the structure from some fields from one of the tables: My issue is that every time I try to enter a new record on the table, there is an error at
How to cascade on delete in a foreign key of a mysql table correctly?
I have three tables with foreign keys which should cascade on delete nevertheless when I run this only the mapping in Tagmap and nothing happens in Excerpt nor Tag. What am I doing wrong? Answer This is not how foreign keys work. You delete from the parent table (here, tag, or excerpt), and then children reco…
Comparing each colum in a row to every row in the database sql
I am building a bot that matches users based on a score they get, this score is taken from calculations done to data in a database on the request of the user. I have only 1 table in that database and a few columns (user,age,genre,language,format,…etc). What I want to do is, once the user clicks “f…