Based from this answer I found one problem. JSON object is return as NULL. Suppose that I have a JSON like this: { “array_in_json”: [ { “number”: 1, “character”: “A”, “some_object”: { “…
Tag: sql-server
Query to find courses that all members are enrolled in
I have a database which contains 5 tables. And I want to find the names of courses that are enrolled by all “Automotive Engineering” students. I tried the below statements but it shows nothing. I execute the above query but it shows nothing. Can someone help me from here? Answer This is a kind of relational d…
System Catalog vs Information Schema
When looking at SQL Server Management Studio (SSMS), which one is better and why? Are there cases where we should use one over the other? I can’t tell the difference between them. Answer INFORMATION_SCHEMA is there for compatibility, it doesn’t expose all the information about objects on the insta…
Fetching Database Name with Space
Thanks for looking into my post. I am trying to run a query across all the database in SQL Server but the cursor is not fetching if the database contains space and the database is online. Code Error Answer Delimit Identify your object’s name. In normal terms that would be wrapping it with brackets ([]),…
SQL Query to return all rows with the earliest date available for each year
I am trying to design a SQL query that can return the records for the earliest date in each year. Suppose I have a table as follows: I am trying to generate a table shown below: The earliest date for each year will change, which means I cannot simply query by day & month. I have tried using: But this
The INSERT statement conflicted with the FOREIGN KEY constraint “FK__Messages__Email__3D9E16F4”
Maybe someone can tell me why this is happening. I wrote this stored procedure: Now I’m trying to check it by inserting: and I get: Msg 547, Level 16, State 0, Procedure Newmessages, Line 4 [Batch Start Line 0] > The INSERT statement conflicted with the FOREIGN KEY constraint “FK__Messages__Ema…
Couldn’t return output with SRC Key in an INSERT statement
I want the OUTPUT clause to return both the source key and the target key like below : I am having this following error : Answer To be clear, in INSERT, UPDATE, and DELETE statements, you can only refer to columns from the target table in the OUTPUT clause. In a MERGE statement you can refer to columns from b…
Single query to split out data of one column, into two columns, from the same table based on different criteria [SQL]
I have the following data in a table, this is a single column shown from a table that has multiple columns, but only data from this column needs to be pulled into two column output using a query: Required result: distinct values based on the first 13 characters of the data, split into two columns based on …
Compare records in same table using self join
I am looking to get Permanent employee who has joining date greater than manager. This is what I tried till now, but I get zero records. I am expecting ouptut as Epmoyee with Id 9 as it has joining year(2019) greater than both the managers(2017 and 2015) Answer The join condition on id is the problem; this is…
How to select and join field with max date?
I have two tables joined on RECID and AAATRANSPORTORDERRECID : AAATRANSPORTTABLE AAALTLCHANGEREQUEST I need to select the record shown from AAATRANSPORTTABLE and join the AAALTLCHANGEVALUE value for the most recent CREATEDDATETIME from AAALTLCHANGEREQUEST. My query is as below: It produces these results: My d…