Obtain missing records from 2 queries not tables

I know how to find a common value between tables that’s missing but two queries? Query 1: SELECT DISTINCT GENG.GlGroup FROM MCMGenHistory GENH RIGHT OUTER JOIN RPZCGenGroup GENG ON GENG.ExpenseCode = substring(GENH.GlCode,13,3) WHERE SUBSTRING(GENH.GlCode,1,1)=’P’ Results: 10 7 8 5 1 6 9 4 Query 2: SELECT DISTINCT GENG.GlGroup from RPZCGenGroup GENG Results: 1 10 2 […]

How to select a string between 2 identical characters in SQL

My initial string is Content-Disposition: attachment; filename=”0001.zam” I want to select everything between the two ” characters (“0001.zam” in this case). I know that I need to use the SUBSTRING and CHARINDEX functions similar to: SELECT SUBSTRING(@Attachment, CHARINDEX(‘”‘, @Attachment),…) I can’t figure out what to pass as the second SUBSTRING argument. Note that the string […]

Performing a SUM when value could exist in one of a few different columns

Assume there is a simple table: CREATE TABLE [dbo].[foo]( [foo_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [val1_id] [int] NULL, [val1_amount] [int] NULL, [val2_id] [int] NULL, [val2_amount] [int] NULL, [val3_id] [int] NULL, [val3_amount] [int] NULL, [val4_id] [int] NULL [val4_amount] [int] NULL, ) ON [PRIMARY] And there is some other table that is: CREATE TABLE [dbo].[val]( […]

SQL code to extract values separated by commas in a multivalued field

How I can do this using SQL Server?

Finding Top Level Parent ID

In one table I have two columns like below ID ParentID 1 0x0 2 1 3 2 9 0x0 5 9 6 5 25 0x0 30 25 How to get top level parent ID The result should look like ID TopParentID 3 1 6 9 30 25

Query to output not existing data

Table A: id Name 1 a 2 b 3 c 4 d 5 e Table B: id Name 3 c 4 d 5 e Here, id is the primary key connected to Table B. I need output like this:- id 1 2 That means, which ids in Table A are not present in Table B

Alternative to DISTINCT Function

Is there a better way to get all distinct values from three columns in one table other than using the DISTINCT function? I’ve also tried GROUP BY, but there doesn’t seem to be any noticeable difference in the cost. SELECT DISTINCT Table1.Col1, Table2.Col1, Table1.Col3 FROM Table1 INNER JOIN Table2 ON Table1.FK = Table2.ID WHERE Table1.Foo […]

Perform line sorting SQL Server

Is this possible? For example, I want to get the latest date for the value of PC2Date,Re2Date,Rp2Date then the resut will be the value of PC2 or Re2 or Rp2. I want to add another column for the Result of the sorting. +——————————————————————————————————+ |id | Pc2 | Pc2Date | Re2 | Re2Date |Rp2 | Rp2Date […]

Which query is more appropriate?

TABLE T1 TABLE T2 +—-+————+ +—-+————+ | Id | Name | | Id | Some_Data | +—-+————+ +—-+————+ | | | | | | Query1: SELECT * FROM T1 JOIN T2 ON T1.Id=T2.Id WHERE T1.Id=1001 Query2: SELECT * FROM T1 JOIN T2 ON T1.Id=T2.Id WHERE T2.Id=1001 If T2 has 10 million rows of which only […]

Why can't I apply a criteria on a sub-query?

What I’m trying to do is this: select Store.Id, (select COUNT(*) from StoreProduct where StoreProduct.Store_id = Store.Id) as _count from Store where _count > 3 SQL Server says it’s invalid because the column name ‘_count’ is invalid. Why it’s invalid if I’m declaring it?

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.