I have 2 main table in MYSQL DB. I will call it entityA and entityB. There are some logic that user will start to create a entityA and then convert it to entityB but some time they will not create entityA and only create entityB Therefore, I have created a middle table which stored entityAId and entityBId. en…
Tag: sql
Equivalent of jsonb_path_query_array in Postgresql-11
Given a column containing the above data, I use select jsonb_path_query_array(column, ‘$.key’) to get the output [KA, KB] However this doesn’t work in Postgres-11. Are there any alternatives for the same ? Answer Yes. This yields a Postgres array. Use jsonb_agg instead of array_agg if you ne…
How to add data to a field based on other fields in a SQL database
I have an SQLite table called wcvp constructed from a csv file downloaded from the World Check List of Vascular Plants (see https://wcvp.science.kew.org/ and http://sftp.kew.org/pub/data-repositories/WCVP/). When I run this query: I get this result: kew_id genus species infraspecies 304293-2 Quercus robur 771…
Single table or multiples tables (multiple company database)
I have to create a database for multiple companies, I have 2 options A single products table, with a company_id field to differentiate One table per company products_company1 products_company2 products_company3 Important: Data stored of each client will be updated every 1 hour Each company has 2000 – 30…
Stuck on getting deps through a query
I have a table, called refs, which has columns such as: depname, refname, reftype. Where, refname is the name of the table dependent on a view. For this use case, I want to get all the views directly or indirectly dependent on table(s) containing ‘sapphire’ in their name. I’m not sure how to…
spring batch table size limit
I am currently trying to read a table with about 5 million records using spring batch. After several minutes I got the follwing exception: org.springframework.batch.item.ItemStreamException: I am using a simple query: select * from table When using a query with a limit like select * from table limit 10000 it …
Most elegant way to search for a json value and replace it in mysql? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 1 year ago. Improve this question I have a table called Entity which stores a bunch of jsons. The Json is bas…
SQL SUM values by DISTINCT column after JOIN
I’ve been searching the forums for a solution for hours now but I couldn’t quite find the one that is fitting for my problem. I have two tables: members **************** id name city 1 John Boston 2 Maria Boston 3 Steve London 4 Oscar London 5 Ben Singapore donations ******************** member_id…
Need to handle custom query summation for group
I have this table name “TB1” contain of these columns : I need a group with Bill.NO with sum column qty ,price from “TB1” to be like this : What I tried to do , I wrote this query but not doing as I want : Answer Really, you should leave these kind of things to the presentation layer. …
In SQL, how do I coalesce the first value of 3 columns that is 10 characters long
I am trying to only select one column from 3 distinct phone number columns, and I would prefer to have the column with the longest phone number (some don’t have area codes). Currently, I have coalesced the 3 columns as some are also null, with a preference on cell phone. Ex. What I’ve tried : I wo…