I have a postgresql table with two columns. name VARCHAR(255) and notes JSON. A sample dataset might look like this: Now I want to do two things. put the list from the json at ‘link_to’ into another column called referrals_to (which must be of array VARCHAR type then). From my example: name notes referrals_to ‘anna’ {‘link_to’: [‘bob’]} [‘bob’] ‘bob’ {‘link_to’:
Category: Questions
How do I do nested if statements in SQL?
Write a program that will check the answers for the latest test. The program will be given a table answers with the following columns: id – the unique ID of the question; correct_answer – the correct answer to the question, given as a string; given_answer – the answer given to the question, which can be NULL. Your task is to
Turn these temp tables into one longer subquery (can’t use Temp tables in Power BI)
Currently I have created these temp tables to get the desired output I need. However, Power BI doesn’t allow the use of temp tables so I need to get this all into 1 query using inner selects. This code is simple and straight forward. However I can’t seem to figure out using a subquery. Here is what I have. It
Update of many rows with join extremely slow
I have a table with five relevant fields – id, source, iid, track_hash, alias. I want to group all entries into groups with a common track_hash and then for each row save the id of the row with the lowest source (with ties broken in favor of the highest iid) entry from its group into the alias field. To do
Convert values in related table to comma-separated list
I have two SQL Server tables: I’m trying to the following result where the related activities are in a comma-separated list: I tried: Can someone help me with this SQL query? Thank you. Answer You could use OUTER APPLY to aggregate the string if you’re using SQL Server 2017 or higher.
Compare two tables via the three tables SQL
I plan to compare two tables via the three table. my query is as the following if the count is zore, then the tableA and TableC match, otherwise, these two tables do not match It takes a long time to run the query. Do we have a way to compare tableA and tableC fast? Question: How to compare tableA and
Converting string to a date but date not formatted properly
I am having issues with converting a string into a date. The issue is that the format of the string does not easily convert to a date. For example, all the dates are set up like APR-19, or JAN-21. I am not sure how to properly cast this. Here is the SQL I am using. Answer in PostgreSQL it is
Getting values from 3 tables where two of those are self-joined
Problem Introduction: I’m working on a faker e-banking system as a side project for practicing my SQL skills (which are super bad), and I’m now stuck at a point where I want to pull data from two tables but cannot do that with joins So, basically I have a users table which looks like this: And I have a transactions
Invalid reference to FROM-clause entry for table “mc”
I’m trying to get an item “manufacturer” with an array of contacts and each contact contains an array of translations using this query but I get this error this is the ER diagram dummy data example: I tried to change the structure of the query to solve it but I wasn’t able to fix it. Since I don’t have much
SQL Server query to check if date has the same digit
case statement to validate if date column is all the same number number (ie. 1s, 2s, etc) DECLARE @opendate varchar SET opendate = ‘11111111’ SELECT CASE WHEN opendate LIKE ‘^([0-9a-z]…