ALTER PROCEDURE discussions_GetTopics
(@board_id as int)
AS
SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Topics.topic_id = discussions_Posts.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_1.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @board_id)
I am simply trying to return a result for each topic, that has user info for both the author of the topic and the author of the last post (user_1, user_2)
The problem is, it will return multiple datarows with the same topic, and each of them have a different last post author.. when there can only be one last poster... idk.. im confused.. help?
I have a client who accidentally deleted data from a table this morning around 10:15 AM. Classic case of accidentally did not highlight the WHERE condition in the DELETE statement and ran it.
1. Database is in Full Recovery mode. 2. Unable to locate the last backup file. 3. Transaction Log file is around 128GB while the mdf is around 212 GB.
Is there a way we could restore the database to a new name and take it back to 10AM, get the table data back and re-insert into the production database?
I'm new to SQL and tried to develop a simple query on multiple tables of a database. The query works but returns duplicate records even though there aren't duplicate records in the database.
Here is the script:
SELECT LelyDevice.iAddress, LelyDeviceVisit.tEndVisitDateTime, LelyCow.dBirthDate, LelyCow.iUserNumber, LelyAstronautVisit.lRefusal, LelyAstronautVisit.lFailedMilking, LelyMilkVisitData.nMilkYield, LelyLactationProduction.dCalvingDate FROM LelyDevice INNER JOIN LelyDeviceVisit ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice INNER JOIN LelyCow ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow INNER JOIN LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit INNER JOIN LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit INNER JOIN LelyLactationProduction ON LelyCow.PK_LelyCow = LelyLactationProduction.FK_LelyCow
WHERE LelyDeviceVisit.tEndVisitDateTime BETWEEN '2006-07-29 03:20:00.000' AND '2006-08-03 08:58:59.000' AND LelyAstronautVisit.lFailedMilking = 1 ORDER BY LelyDeviceVisit.tEndVisitDateTime DESC
and here is the result: 13012006-08-03 08:57:39.0002002-07-03 00:00:00.000209015.12005-06-02 00:00:00.000 13012006-08-03 08:57:39.0002002-07-03 00:00:00.000209015.12004-06-06 00:00:00.000 13012006-08-02 20:21:54.0002002-07-03 00:00:00.000209012.72005-06-02 00:00:00.000 13012006-08-02 20:21:54.0002002-07-03 00:00:00.000209012.72004-06-06 00:00:00.000 11012006-08-02 19:26:44.0002002-11-20 00:00:00.000221010.02005-03-27 00:00:00.000 13012006-08-02 19:16:33.0002002-11-20 00:00:00.000221012.32005-03-27 00:00:00.000
As you can see the first 2 lines are the same date and time and the next 2 and so on.
I have the following query. I want it to find how many minutes used by MDN (which is the column for the phone numbers in my database). It keeps giving me duplicate MDN's. How can I get it to give me the total minutes used by an MDN per day?
SELECT DISTINCT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE Durationseconds >0 and CallDate >= '02/19/2007' and calldate < '02/20/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN UNION SELECT DISTINT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE Durationseconds > 0 and CallDate >= '02/19/2007' and calldate < '02/20/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN order by mdn
I have duplicate results in from my query. I am using the distinct clause but this does not seem to be working. In the example below, I do not want the 79.15 to repeat multiple times eventhough the first column has all distinct values. I am using distinct in my select but this is not stopping the duplicates on the second row. Any guidance would be greatly appreciated. Thanks
I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.
Values in the table
Code: name surname status Closed Number ----------- --------- ----------- ------------- ---------------------------- Jeff Burns closed 2012/01/01 142 Tina Drewmor closed 2008/05/20 546 Jeff Burns active 1900/01/01 354 Kyle Higgin active 1900/01/01 851 Tina Drewmor closed 2009/04/14 154
The query I am using so far is:
Code: select d.name, d.surname, s.status, s.closed, s.number from d d inner join s s on d.number = s.number where s.status = 'closed'
What I need to see in the results
Code: name surname status Closed Number ----------- --------- ----------- ------------ ----------------------------- Tina Drewmor closed 2008/05/20 546 Tina Drewmor closed 2009/04/14 154
I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.
The query should have a surname column, a forenames column, and two id columns (from the person column of the table).
I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.
This is what i have done
select first.surname, first.forenames, first.person, second.person from shared.people first, shared.people second where first.surname= second.surname and first.forenames = second.forenames and not first.person = second.person order by first.surname, first.forenames
and i get results like this
Porter Sarah Victoria 9518823 9869770 Porter Sarah Victoria 9869770 9518823 - i.e. duplicates
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER FROM VENDFIL A, VENDFIL B, VENDFIL C GROUP BY A.FEDTID, B.VENDOR
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:
I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:
SELECT A.AccountID, c.CustomerNo, c.Surname, c.Name, c.TaxNum FROM Accounts A left join AccountCustomer ac on ac.AccountID = A.AccountID left join Customers c on c.CustomerNo = ac.CustomerNo order by A.AccountID;
As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:
I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to? Thanks!
How can I compare numerous rows in the same table that have the same fname, lname and access_id but different identity values (and other columns) and then keep only the one row which is the most current based on a date col?
This seems so easy, but today I just can't come up with it. Do I have to use a cursor of some kind?
insert into Table1 select distinct FKID from Table2 where FKID not in (select PKID from Table1)
The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..
Hi! Very grateful for some advice... duplicate key.. but how do I solve it. By Lookup ? [OLE DB Destination [1647]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".
OLE db SQL: SELECT DISTINCT convert (char(8),date,112) as day, cast(datepart(year, date) as varchar(4)) + cast(datepart(week, date) as varchar(2)) as weeknr, cast(datepart(year, date) as varchar(4)) + Substring(convert(varchar(10), date, 121), 6, 2) as month, YEAR(date) AS year FROM Purchase
I'm currently working on a Game DB, To where i can set Primary Key's and such, otherwise the Emu reads it wrong. The current table i am working on, has duplicate row's of every row. (3600 rows instead of 1800) and the duplicates have the same ID as the original row. How would i go about Deleting the Duplicate rows without harming the original? (Sql 2005)
I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.
I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.
When I try to execute this in a DTS package, it stops the package execution because an error message was returned.
I have tried setting the fail on errors to OFF, but this has no effect.
I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.
I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.
I have verified the service pack, re-booted, etc.....
Hello guys! Is it possible to duplicate a primary key? I would like my database to accept data with the same primary key. Is it possible? How do you declare ON DUPLICATE KEY UPDATE? Please help me. Thanks in advance.
hello everyone, how to prevent putting same value into the column in the sqldatabase? while inserting value into the table from asp.net page dynamically,i dont want user to insert same data again and again. is there anyway i can make check the column in the database to look for the duplication value. if not then only i want to make it insert. thanks. jack.
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record.
I used the following select statement to get duplicate records on Case_number column
select cases.distinct case_link, cases.case_number from cases group by case_link having case_number > 1
I got the error message that
"'cases.warrant_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. AND cases.case_number' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Any idea on a better statement to use. THANKS FOR YOUR HELP!