sql

SQL Count unique objects defined by parameters

I have a table with: id | parameter 1 | A 1 | B 2 | A 3 | A 3 | B That represent objects defined with the values as: 1 -> A,B 2 -> A 3 -> A,B I want to count the number of objects with different parameters using a SQL query, so in this case it would be 2 unique objects as 1 and 3 have the same parameters. There is no const...

ASP.NET MVC with SQL Server backend returns old data when query is executed

My ASP.NET MVC web app has a weird issue. In VS debugging mode it works as expected, but when I publish it to dedicated web server (windows 2003, IIS6) a sql query returns previous data even though underlying data was already updated through the same connection. It looks like as if a query returns cached data. What might be the problem? ...

SQL server virtual memory usage and performance

Hello, I have a very large DB used mostly for analytics. The performance overall is very sluggish. I just noticed that when running the query below, the amount of virtual memory used greatly exceeds the amount of physical memory available. Currently, physical memory is 10GB (10238k bytes) whereas the virtual memory returns significantly...

ASP.NET configure data source is not returning anything?

I'm selecting table data of the current user: SELECT [ConfidenceLevel], [LoveLevel], [HappinessLevel] FROM [UserData] WHERE ([UserProfileID] = @UserProfileID) I have set a control to the unique user ID and it is getting the correct value: HTML: <asp:Label ID="userID" runat="server" Text="Labeluser"></asp:Label> C#: userID.Text = M...

How to Check all stored procedure is ok in sql server?

How to check all stored procedure is ok in sql server if I drop a table or fields? ...

Specific cast is not valid, while retrieving scope_identity

I am getting exception: "Specific cast is not valid", here is the code con.Open(); string insertQuery = @"Insert into Tender (Name, Name1, Name2) values ('Val1','Val2','Val3');Select Scope_Identity();"; SqlCommand cmd = new SqlCommand(insertQuery, con); cmd.ExecuteNonQuery(); tenderId = (int)cmd.ExecuteScalar(); ...

SQL select statement

Hi all, I got a Table which has two fields: Point, and Level, with some sample data as follows: ----------------------- Point | Level ----------------------- 10 | Level 1 20 | Level 2 30 | Level 3 40 | Level 4 Suppose that there is a user who has 25 points, to find the Level in which this user is i...

How to Unselect The Field in select Query using sql

hi in my database i am store more than 50 field with primarykey (Auto increment) i am not sure about the fields name but i wants to select the entire data in that table , i am using SELECT * FROM tablename i want to select all the fields except that ID but this query populate the entire table so is there is possible to unselect the pa...

How do I get the desired result in T-SQL like ....

How do I get the desired result in T-SQL like .... like I have a Record like UseriD InDate outDate 1 3/12/2010 3/12/2010 1 3/12/2010 3/13/2010 1 3/19/2010 3/30/2010 2 3/2/2010 3/3/2010 2 3/3/2010 3/4/2010 2 3/4/2010 3/29/2010 3 2/2/2010 2/28/2010 so...

SQL-Join with NULL-columns

I'm having the following tables: Table a +-------+------------------+------+-----+ | Field | Type | Null | Key | +-------+------------------+------+-----+ | bid | int(10) unsigned | YES | | | cid | int(10) unsigned | YES | | +-------+------------------+------+-----+ Table b +-------+------------------+------+ ...

how to select rows from table.

my table contains category_name and parent_category_Id column My parent_category data contains, the same table primary key id. i need to select the all rows and insted my parent_category_Id i need to select the catogry_name ...

complicated inserts

I have to do something like this insert into object (name, value, first_node) values ('some_name', 123, 0) @id = mysql_last_insert_id() insert nodes (name, object_id) values ('node_name',@id) @id2 = mysql_last_insert_id() update object set first_node=@id2 where id=@id Is it possible to make it simpler? What if I want to insert more pa...

Which database invented the "limit" SQL query syntax?

MySQL has a nice feature (although non standard) which allow to query resultsets' limit, offset as SELECT * FROM TABLE LIMIT M, N; Is it created by MySQL? or Postgres? ...

SQL Having Clause

I'm trying to get a stored procedure to work using the following syntax: select count(sl.Item_Number) as NumOccurrences from spv3SalesDocument as sd left outer join spv3saleslineitem as sl on sd.Sales_Doc_Type = sl.Sales_Doc_Type and sd.Sales_Doc_Num = sl.Sales_Doc_Num where sd.Sales_Doc_Type='ORDER' and sd.Sales_Doc_Num='OREQ...

2-column table with two foreign keys. Performance/design question.

Hello everyone! I recently ran into a quite complex problem and after looking around a lot I couldn't find a solution to it. I've found answers to my questions many times before on stackoverflow.com, so I decided to post here. So I'm making a user/group managment system for a web-based project, and I'm storing all related data into a p...

SQL query through an intermediate table

Given the following tables: Recipes | id | name | 1 | 'chocolate cream pie' | 2 | 'banana cream pie' | 3 | 'chocolate banana surprise' Ingredients | id | name | 1 | 'banana' | 2 | 'cream' | 3 | 'chocolate' RecipeIngredients | recipe_id | ingredient_id | 1 | 2 | 1 | 3 | 2 | 1 | 2 | ...

I would like a simple example of a sub-query using T-SQL 2008

Can anyone give me a good example of a subquery using TSQL 2008? ...

SQL Query that can return intersecting data

I have a hard time finding a good question title - let me just show you what I have and what the desired outcome is. I hope this can be done in SQL (I have SQL Server 2008). 1) I have a table called Contacts and in that table I have fields like these: FirstName, LastName, CompanyName 2) Some demo data: FirstName LastName CompanyName...

Why can't I pass in a uniqueidentifier/GUID to a stored procedure

Hi I have this SP USE [TestDB] GO /****** Object: StoredProcedure [dbo].[sp_test] Script Date: 06/12/2010 11:47:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_test] @id uniqueidentifier AS BEGIN select * from TestTbl where ProductId= @id END I then went to the SP with ms sql 20...

filter duplicates in SQL join

When using a SQL join, is it possible to keep only rows that have a single row for the left table? For example: select * from A, B where A.id = B.a_id; a1 b1 a2 b1 a2 b2 In this case, I want to remove all except the first row, where a single row from A matched exactly 1 row from B. I'm using MySQL. ...