I got a lot of instances of the same Class. Now these objects can be linked and this link can have a weight. Like in an undirected graph. Now I want to store the relationship between each two objects in my mysql database. Data looks like this I could do create a table with this structure: object1_id | object2…
Tag: sql
How to select distinct value from multiple tables given a column filter?
I have this table Products Id ProductName ProductCode 1 Alexa P0001 2 Alexa P0002 3 Alexa 2 P0003 4 Aquarium P0004 5 Aquarium X P0004 6 Bathtub P0005 Scenario 1: Expected Result: Returns distinct ProductName along with Id Id ProductName 1 Alexa 3 Alexa 2 4 Aquarium 5 Aquarium X Actual result: Returns all prod…
Using the Union function in SQL to reverse columns
I have the following SQL code to produce the following table of data: I am hoping to transform data to create a table that looks like: What would be the best way to achieve this? Something like creating two tables and then a Union join? Answer You can use self join to achieve what are you looking for. Schema:…
How to loop array of objects from table column?
Table columns : My table column details has json object like I want to write a query which will give me records like below : I tried with RESULT WITH COUNT : EXPECTED Answer To get all the objects from json array along with other columns you can use OpenJson() and Cross Apply as below: Query: Output: id cnt n…
Update SQL database registers based on JSON
I have a table with 30k clients, with the ClientID as primary key. I’m getting data from API calls and inserting them into the table using python. I’d like to find a way to insert rows with new clients and, if the ClientID that comes with the API call already exists in the table, update the existi…
How to subtract the cumulative values of confirmed, recovered and deseased?
I would like to subtract the cumulative values of confirmed, recovered and deceased. However, it does not work, Google BigQuery keeps giving me this error message (look at the pic). All attributes are INTEGER. Answer The error message is pretty clear. You have mixed types in the data — suggesting that s…
SQL – Cast Column as XML then Query Value from said column
I have a column with a bunch of unformatted XML code. I am trying to really just query 1 value out of the column. The value inside of the column is listed below: The value that I am looking for is displayValue=”NSharePoint Read Item” which is located in the line: I have the following query: which …
SQL Find list of tuples that fulfil at least one of the conditions
Question: Find the restaurant A that fulfils at least one of the following conditions: Conditions Restaurant A that is situated in ‘Central’ area or Restaurant A that sells at least 10 pizzas or Price of every pizza sold by A is at most $20 Expected outcomes: List with (restaurant name) Database S…
Multiple dynamic FIND_IN_SET searches
So, I have an array like this in my programming language: And I have a MySQL database table like this: Unfortunately, I cannot change the table to be more relational (yet). I want to pull out how many times an item in my array is present in the database value column. Now, the obvious way to do this would be
SQL syntax for characters not in brackets
I’m accessing a Microsoft Access database using ODBC. According to the w3schools SQL tutorial, the ANSI-92 wildcard for “any character not in brackets” should be ^. However, their own …