Retrieve rows that appears more than one time

Let’s say that I have table A and table B, the relationship between them is 1 to many, how do I retrieve only the rows which the A PK appears more than once? in the table B as a FK? I have no idea how could I do this.

Query a time series, update each row with start date of a shift that overlaps to a new day

I have a table that contains some shift information for an employee(s) +——-+————+——————+————+——————+————+ | empid | StartDate | StartTime | EndDate | EndTime | ShiftDate | +——-+————+——————+————+——————+————+ | 391 | 2014-12-16 | 20:00:00.0000000 | 2014-12-16 | 22:00:00.0000000 | ? | | 391 | 2014-12-16 | 22:00:00.0000000 | 2014-12-16 | 22:15:00.0000000 | ? | | 391 […]

Invalid object dbo.UserShoppingItems – Azure IIS & SQL server

Have created two table ‘UserShoppingItems’ & ‘UserShoppingBills’ both in Azure database and local database (sqlexpress 2008 r2). I had manually created entity mapping for the tables using dbcontext class (not used entity database wizard) like ‘UserShoppingItem’ and mapping with the following code. “public DbSet<UserShoppingItem> UserShoppingItems {get; set;}” I could see that table exists in […]

SQL Query returns multiple rows of the same record when View includes one-to-many table

In MS-SQL, I have a View ‘ListingResult’ which contains rows from tables ‘ListingCategoryXref’ and ‘Listing’. This is the View statement: SELECT dbo.Listing.ListingName, dbo.Listing.ListingId, dbo.ListingCategoryXref.CategoryId FROM dbo.Listing INNER JOIN dbo.ListingCategoryXref ON dbo.Listing.ListingId = dbo.ListingCategoryXref.ListingId GROUP BY dbo.Listing.ListingName, dbo.Listing.ListingId, dbo.ListingCategoryXref.CategoryId Listings can have many rows in ListingCategoryXref, thus. ListingResult (View) Listing (table) ListingId ListingName StateId 1 Toms […]

Copy data to nested list

I’ve some students’ records in database and I need to show that data to user in nested grid. Data is like Name – Age – Subject – Obt.Marks – TotalMarks Ali 20 Maths 80 100 Ali 20 Literature 60 100 Ali 20 English 80 100 Ahmad 20 Maths 70 100 Ahmad 20 Literature 60 100 […]

Converting Data Table to List and vice versa

I don’t know what’s wrong. I have a SQL Statement and an adapter DataTable datTableCur = new DataTable(); datTable = new DataTable(); sqlCmd = new SqlCommand(@”SELECT DISTINCT [” + form1.getCol() + “] FROM [” + form1.getTableName3() + “]”, connection); sqlDatAdapter = new SqlDataAdapter(sqlCmd.CommandText, connection); sqlDatAdapter.Fill(datTableCur); Since the format is “float” in SQL I convert it […]

Check if View exists before querying it

I want to check if a specific View exists before querying it. I use dynamic SQL to create the query: DECLARE @sqlCommand varchar(1000) DECLARE @viewName varchar(1000) DECLARE @otherDB varchar(1000) SET @sqlCommand = ‘IF EXISTS(SELECT 1 FROM ‘ + @otherDB + ‘.sys.views WHERE name=”’ + @viewName + ”’) BEGIN SELECT * FROM ‘ + @viewName + […]

SQL Server Compact Edition – Obtain create script

I normally live in a MySQL world where I can use the SHOW CREATE TABLE <tablename> syntax to get the create script of a given table. I’m working with a legacy SQL Server CE 3.5 database and need to get the create script for all of the tables so I can move them into another […]

SQL Combine Multiple rows into Multiple Columns

I have been looking at the other example here and across the web and I just cannot wrap my head around how to do this. I have a site where I am pulling every piece of information on products into the one table to be viewed and then loaded into a csv/excel sheet. It combines […]

How to force “where” precedence when expanding views

Say that I define: USE tempdb; GO CREATE TABLE [dbo].[Test1] ( [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY, [DateString] [VARCHAR](max) ) GO INSERT INTO [dbo].[Test1](DateString) VALUES (‘2014-10-20’),(‘BadDate’); GO CREATE VIEW [dbo].[vw_Test1] AS SELECT Id, CONVERT(DATE,DateString) ConvertedDate FROM dbo.Test1 WHERE isdate(DateString)=1 GO The following query works fine: SELECT * FROM [dbo].[vw_Test1] The following query throws “Conversion […]

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