Search Results

Search found 34476 results on 1380 pages for 'sql blog'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 347/1380 | < Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >

  • How to list all duplicated rows which may include NULL columns?

    - by Yousui
    Hi guys, I have a problem of listing duplicated rows that include NULL columns. Lemme show my problem first. USE [tempdb]; GO IF OBJECT_ID(N'dbo.t') IS NOT NULL BEGIN DROP TABLE dbo.t END GO CREATE TABLE dbo.t ( a NVARCHAR(8), b NVARCHAR(8) ); GO INSERT t VALUES ('a', 'b'); INSERT t VALUES ('a', 'b'); INSERT t VALUES ('a', 'b'); INSERT t VALUES ('c', 'd'); INSERT t VALUES ('c', 'd'); INSERT t VALUES ('c', 'd'); INSERT t VALUES ('c', 'd'); INSERT t VALUES ('e', NULL); INSERT t VALUES (NULL, NULL); INSERT t VALUES (NULL, NULL); INSERT t VALUES (NULL, NULL); INSERT t VALUES (NULL, NULL); GO Now I want to show all rows that have other rows duplicated with them, I use the following query. SELECT a, b FROM dbo.t GROUP BY a, b HAVING count(*) > 1 which will give us the result: a b -------- -------- NULL NULL a b c d Now if I want to list all rows that make contribution to duplication, I use this query: WITH duplicate (a, b) AS ( SELECT a, b FROM dbo.t GROUP BY a, b HAVING count(*) > 1 ) SELECT dbo.t.a, dbo.t.b FROM dbo.t INNER JOIN duplicate ON (dbo.t.a = duplicate.a AND dbo.t.b = duplicate.b) Which will give me the result: a b -------- -------- a b a b a b c d c d c d c d As you can see, all rows include NULLs are filtered. The reason I thought is that I use equal sign to test the condition(dbo.t.a = duplicate.a AND dbo.t.b = duplicate.b), and NULLs cannot be compared use equal sign. So, in order to include rows that include NULLs in it in the last result, I have change the aforementioned query to WITH duplicate (a, b) AS ( SELECT a, b FROM dbo.t GROUP BY a, b HAVING count(*) > 1 ) SELECT dbo.t.a, dbo.t.b FROM dbo.t INNER JOIN duplicate ON (dbo.t.a = duplicate.a AND dbo.t.b = duplicate.b) OR (dbo.t.a IS NULL AND duplicate.a IS NULL AND dbo.t.b = duplicate.b) OR (dbo.t.b IS NULL AND duplicate.b IS NULL AND dbo.t.a = duplicate.a) OR (dbo.t.a IS NULL AND duplicate.a IS NULL AND dbo.t.b IS NULL AND duplicate.b IS NULL) And this query will give me the answer as I wanted: a b -------- -------- NULL NULL NULL NULL NULL NULL NULL NULL a b a b a b c d c d c d c d Now my question is, as you can see, this query just include two columns, in order to include NULLs in the last result, you have to use many condition testing statements in the query. As the column number increasing, the condition testing statements you need in your query is increasing astonishingly. How can I solve this problem? Great thanks.

    Read the article

  • c# linq to sql join problem

    - by b0x0rz
    i am trying to do using (UserManagementDataContext context = new UserManagementDataContext()) { var users = from u in context.Users where u.UserEMailAdresses.EMailAddress == "[email protected]" select u; return users.Count(); } however, when i get to: using (UserManagementDataContext context = new UserManagementDataContext()) { var users = from u in context.Users where u.UserEMailAdresses. i do not get offered the EMailAddress name, but rather some neutral default-looking list of options in intelisense. what am i doing wrong? table Users ID bigint NameTitle nvarchar(64) NameFirst nvarchar(64) NameMiddle nvarchar(64) NameLast nvarchar(64) NameSuffix nvarchar(64) Status bigint IsActive bit table UserEMailAddresses ID bigint UserID bigint EMailAddress nvarchar(256) IsPrimary bit IsActive bit obviously, 1 user can have many addresses and so Users.ID and UserEMailAddresses.UserID have a relationship between them: 1 to MANY.

    Read the article

  • tricky SQL when joining

    - by Erik
    I've two tables, shows and objects. I want to print out the latest objects, and the shownames for them. Right now I'm doing it this way: SELECT MAX(objects.id) as max_id, shows.name, shows.id FROM shows, objects WHERE shows.id = objects.showId GROUP BY shows.name however, if I also want to fetch the episode of the object I can't put it like SELECT object.episode [...], because then wont automatically select the object which is MAX(objects.id), so my question is how to do that? If you haven't already figured out my tables they're like this: Shows id name and also: Objects id name episode season showId Using MySQL. Thanks!

    Read the article

  • Keeping DB Table sorted using multi-field formula (Microsoft SQL)

    - by user298167
    Hello Everybody. I have a Job Table which has two interesting columns: Creation Date and Importance (high - 3, medium 2, low - 1). Job's priority calculated like this: Priority = Importance * (time passed since creation). The problem is, Every time I would like to pick 200 jobs with highest priority, I dont want to resort the table. Is there a way to keep rows sorted? I was also thinking about having three tables one for High, Medium and Low and then sort those by Creation Date. Thanks

    Read the article

  • Getting the ranking of a photo in SQL

    - by Jake Petroules
    I have the following tables: Photos [ PhotoID, CategoryID, ... ] PK [ PhotoID ] Categories [ CategoryID, ... ] PK [ CategoryID ] Votes [ PhotoID, UserID, ... ] PK [ PhotoID, UserID ] A photo belongs to one category. A category may contain many photos. A user may vote once on any photo. A photo can be voted for by many users. I want to select the ranks of a photo (by counting how many votes it has) both overall and within the scope of the category that photo belongs to. The count of SELECT * FROM Votes WHERE PhotoID = @PhotoID being the number of votes a photo has. I want the resulting table to have generated columns for overall rank, and rank within category, so that I may order the results by either. So for example, the resulting table from the query should look like: PhotoID VoteCount RankOverall RankInCategory 1 48 1 7 3 45 2 5 19 33 3 1 2 17 4 3 7 9 5 5 ... ...you get the idea. How can I achieve this? So far I've got the following query to retrieve the vote counts, but I need to generate the ranks as well: SELECT PhotoID, UserID, CategoryID, DateUploaded, (SELECT COUNT(CommentID) AS Expr1 FROM dbo.Comments WHERE (PhotoID = dbo.Photos.PhotoID)) AS CommentCount, (SELECT COUNT(PhotoID) AS Expr1 FROM dbo.PhotoVotes WHERE (PhotoID = dbo.Photos.PhotoID)) AS VoteCount, Comments FROM dbo.Photos

    Read the article

  • SQL query root parent child records

    - by Vish
    Hi, We have nested folders with parent-child relationship. We use MySQL MyISAM DB. The data is stored in the DB in the following manner. Every time a child folder is created in the nested structure, the previous parentID is added. I want to get the RootFolderID of a folder which is added in the hierarchy as tabulated below. FoldID ParentID |RootFolderID -----------------|------------------- 1 0 | 0 2 1 | 1 3 2 | 1 4 3 | 1 5 4 | 1 Please let me know how to get the root folderID and populate it in the RootFolderID column after a folder is created each time. Thanks.

    Read the article

  • how to enter manual time stamp in get date ()

    - by Arunachalam
    how to enter manual time stamp in get date () ? select conver(varchar(10),getdate(),120) returns 2010-06-07 now i want to enter my own time stamp in this like 2010-06-07 10.00.00.000 i m using this in select * from sample table where time_stamp ='2010-06-07 10.00.00.000' since i m trying to automate this query i need the current date but i need different time stamp can it be done .

    Read the article

  • SQL Server Cursor data as result of stored procedure

    - by Dmitry Borovsky
    Hello, I have a stored procedure DECLARE cursor FOR SELECT [FooData] From [FooTable]; OPEN cursor ; FETCH NEXT FROM cursor INTO @CurrFooData; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CurrFooData AS FooData; INSERT INTO Bar (BarData) VALUES(@CurrFooData); FETCH NEXT FROM cursor INTO @CurrFooData; END; CLOSE cursor DEALLOCATE cursor But in result I have a lot of tables, not one. How can I return one table with 'FooData' column and all '@CurrFooData' rows?

    Read the article

  • Remote SQL login page C#

    - by Crazyd22
    Hey, I am trying to create a login page that checks the username and password with the database on the server. The server is located in a different country. This is the code I have so far: #region Building the connection string string Server = "XX.XXX.XX.XX, XXXX"; string Username = "_Username_"; string Password = "_Password_"; string Database = "_Database_"; string ConnectionString = "Data Source=" + Server + ";"; ConnectionString += "User ID=" + Username + ";"; ConnectionString += "Password=" + Password + ";"; ConnectionString += "Initial Catalog=" + Database; #endregion SqlConnection SQLConnection = new SqlConnection(); try { SQLConnection.ConnectionString = ConnectionString; SQLConnection.Open();   // You can get the server version // SQLConnection.ServerVersion } catch (Exception Ex) { // Try to close the connection if (SQLConnection != null) SQLConnection.Dispose();   // Create a (useful) error message string ErrorMessage = "A error occurred while trying to connect to the server."; ErrorMessage += Environment.NewLine; ErrorMessage += Environment.NewLine; ErrorMessage += Ex.Message;   // Show error message (this = the parent Form object) MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);   // Stop here return; } I am getting the error message: Non-negative number required. Parameter name: count I have accepted wildcards on my server and I have no idea what that error means? Any help would be appreciated, Thanks.

    Read the article

  • Help with SQL Server query

    - by Travis
    Sorry* this is what I should have put My query is creating duplicate entries for any record that has more than 1 instance (regardless of date) <asp:SqlDataSource ID="EastMonthlyHealthDS" runat="server" ConnectionString="<%$ ConnectionStrings:SNA_TRTTestConnectionString %>" SelectCommand="SELECT [SNA_Parent_Accounts].[Company], (SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC) AND ([SNA_Parent_Accounts].[CompID] = [Monthly_HIP_Reports].[CompID])) as [AccountHealth], [SNA_Parent_Accounts].[CompID] FROM [SNA_Parent_Accounts] LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID] WHERE (([SNA_Parent_Accounts].[Classification] = 'Business') OR ([SNA_Parent_Accounts].[Classification] = 'Business Ihn')) AND ([SNA_Parent_Accounts].[Status] = 'active') AND ([SNA_Parent_Accounts].[Region] = 'east') ORDER BY [SNA_Parent_Accounts].[Company]"> <SelectParameters> <asp:ControlParameter ControlID="ddMonths" Name="ToDtRFC" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> Using SELECT DISTINCT appears to correct the problem, but I don't consider that a solution. There are no duplicate entries in the database. So it appears my query is superfically creating duplicates. The query should grab a list of companies that meet the criteria in the where clause, but also grab the Health status for each company in that particular [YearMonth] if present which is what the subquery is for. If an entry for that YearMonth is not present, then leave the Health status blank. but as stated earlier.. if you have an entry say for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2.. Doesn't matter what month you select it will list that company 2-3 times.

    Read the article

  • Database design: Calculating the Account Balance

    - by 001
    How do I design the database to calculate the account balance? 1) Currently I calculate the account balance from the transaction table In my transaction table I have "description" and "amount" etc.. I would then add up all "amount" values and that would work out the user's account balance. I showed this to my friend and he said that is not a good solution, when my database grows its going to slow down???? He said I should create separate table to store the calculated account balance. If did this, I will have to maintain two tables, and its risky, the account balance table could go out of sync. Any suggestion? EDIT: OPTION 2: should I add an extra column to my transaction tables "Balance". now I do not need to go through many rows of data to perform my calculation. Example John buys $100 credit, he debt $60, he then adds $200 credit. Amount $100, Balance $100. Amount -$60, Balance $40. Amount $200, Balance $240.

    Read the article

  • SQL syntax error in Update statement VB.net

    - by Shane Fagan
    Hi, Im getting a strange syntax error when I run this in VB SQLString = "UPDATE Login SET Password = '" + PasswordTextBox.Text + "'" SQLString += " WHERE UserName = '" + UserNameTextBox.Text + "'" The Username is checked before getting to this part and is definitly in the db. It gives an exception saying syntax error in update statement. Anyone have any ideas whats wrong?

    Read the article

  • SQL Server 2005 Create Table with Column Default value range

    - by Matt
    Trying to finish up some homework and ran into a issue for creating tables. How do you declare a column default for a range of numbers. Its reads: "Column Building (default to 1 but can be 1-10)" I can't seem to find ...or know where to look for this information. CREATE TABLE tblDepartment ( Department_ID int NOT NULL IDENTITY, Department_Name varchar(255) NOT NULL, Division_Name varchar(255) NOT NULL, City varchar(255) default 'spokane' NOT NULL, Building int default 1 NOT NULL, Phone varchar(255) ) I tried Building int default 1 Between 1 AND 10 NOT NULL, that didn't work out I tried Building int default 1-10, the table was created but I don't think its correct.

    Read the article

  • How to include SqlExpresss 2008 (conveniently)

    - by Henk Holterman
    When I make a setup project in VS 2008, and select <Setup Project>, Properties, PreRequisites then i can select SqlExpress2005 to be automatically included. What I am looking for is a walkthrough of how to get SqlExpress2008 included in the same manner. Second choice would be how to get (or make) a MergeModule (MSM) file to do the same.

    Read the article

  • SQL - Outer Join 2 queries?

    - by Stuav
    I have two querys. Query 1 gives me this result: Day New_Users 01-Jan-12 45 02-Jan-12 36 and so on. Query 2 gives me this result: Day Retained_Users 01-Jan-12 33 02-Jan-12 30 and so on. I want a new query that will join this together and read: Day New_Users Retained_Users 01-Jan-12 45 33 02-Jan-12 36 30 Do I use some sort of outer join?

    Read the article

  • LINQ2SQL: orderby note.hasChildren(), name ascending

    - by Peter Bridger
    I have a hierarchical data structure which I'm displaying in a webpage as a treeview. I want to data to be ordered to first show nodes ordered alphabetically which have no children, then under these nodes ordered alphabetically which have children. Currently I'm ordering all nodes in one group, which means nodes with children appear next to nodes with no children. I'm using a recursive method to build up the treeview, which has this LINQ code at it's heart: var filteredCategory = from c in category orderby c.Name ascending where c.ParentCategoryId == parentCategoryId && c.Active == true select c; So this is the orderby statement I want to enhance. Shown below is the database table structure: [dbo].[Category]( [CategoryId] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NOT NULL, [Level] [tinyint] NOT NULL, [ParentCategoryId] [int] NOT NULL, [Selectable] [bit] NOT NULL CONSTRAINT [DF_Category_Selectable] DEFAULT ((1)), [Active] [bit] NOT NULL CONSTRAINT [DF_Category_Active] DEFAULT ((1))

    Read the article

  • Stored procedure does not return result when using Temp table

    - by Muhammad Akhtar
    I have used temp table in my stored procedure and unable to get the result when executing. It return result fine when running the same as query. my query structure is something like... ALTER PROCEDURE [dbo].[test] as begin SET NOCOUNT ON; SELECT * INTO #Dates FROM Dates -- Used temp table SET @Query = ' SELECT [Name], [HotelName], '+@Dates+' FROM ( SELECT , HD.[Name], HD.[HotelName], HD.Price FROM #Dates D LEFT JOIN #HotelData HD ON D.DateVal = HD.OccDate) T PIVOT ( SUM(Price) FOR [Date] IN ('+@Dates+') ) AS PT' execute(@Query) end Exec test -- Exectuting, I am getting message command completed successfully

    Read the article

  • PHP SQL Form Insert

    - by Prateek Sachan
    I've developed a form that inserts many things into the database. But somehow, when the page is filled up; it inserts only the user_password that too of the database admin. here is the code. Any help would be great. Invalid Name: We want names with more than 3 letters. Invalid E-mail: Type a valid e-mail please. Passwords are invalid: Passwords doesnt match or are invalid! Please enter your contact number. Please enter your age Congratulations! All fields are OK ;)

    Read the article

  • Oracle SQL Update query takes days to update

    - by B Senthil Kumar
    I am trying to update a record in the target table based on the record coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. I find that the Informatica code is perfectly fine looking at the Informatica session log but its in the update it takes long time (more than 5 days to update one million records). Any suggestions as to what can be done on the scenario to improve the performance?

    Read the article

  • php, sql selection

    - by cosy
    I have a stupid question, I have this table : id_product name value 1 price 10-20 1 type computer 2 price 20-30 3 price 100-200 and I want to select from this table GROUP BY id_product and ORDER BY value WHERE name='price' how can i do this? Thanks a lot

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >