Let’s say that I have a table called queries, which holds hundreds of predefined SQL queries in JSON format. Then let’s say that I have two different databases, the original being a 200+ table DB and the new one being a 50~ table DB. This new DB is a subset of the original, so every table it has e…
SQL – Compare 2 tables and show non existent data
Ok, I have 2 tables that I need to do a comparison to. The idea is to show anyone in the database who has not purchased a specific product. Table 1 UserID Customer ProductsSold 1 John Cookies 2 …
SQL Select to display one to one, one to many from many to many relation
I’m trying to figure out SQL query that will list only one to one relation from below table. Below Table contain 10 record where 8 are many to many relation data and 2 are one to one relation. Request your help with SQL that I can use to query below table and list out 2 records that has one to
Creating table script with values in codebehind inC#
I want to build a console application with ado.net where I get a table from the database and convert it to a string output in a file. I want to do this with stringbuilder. But before I fetch the data …
how to improve mysql query speedy with indexes?
I must run this query with MySQL: select requests.id, requests.id_temp, categories.id from opadithree.requests inner join opadi.request_detail_2 on substring(requests.id_sub_temp, 3) = …
How to deselect duplicate entries in a query?
I’ve got a query like this: and this query returns the result like this: As you can see in the image there is 3 duplicate, unnecessary entries (no, i can’t delete them because of the multiple foreign keys). How can I deselect these duplicate entries from the result query? In the end I want to retu…
getting different results from the same query according to the value of a variable
suppose we have the following masterdata: and we have the order tables: what is requested is to have one single query that could return 2 different sets of data, according to the value of a given variable v_order_status: v_order_status = ‘del’ then only transport orders is retrieved: v_order_statu…
Why do I need to declare variables twice: within and without the dynamic query?
I’m reworking an existing static query to be dynamic. Why am I needing to declare the variables twice? Once at the top of the file (for a subsequent, unchanging query) and again within the dynamic …
how to combine multiple rows into one row?
My table: What i want to get is: Is there any simple way for this? Answer If you are using MySQL 8+, then ROW_NUMBER combined with pivoting logic provides one way:
Combining MAX and SUM in an Oracle ROLLUP
Here’s my SQL: The output looks like this: I was expecting it to look like this: In other words, I was expecting the rollup to sum up the maximum weights of each food instead of taking the maximum of all the maximums in the food-type category. I do have a solution of sorts, but it means having to add an