How To Avoid Duplicate Cells When Trying To Join 3 Tables?
Jun 3, 2008
Hi all,
Below are my tables:
Rowid
Name
1
John
2
Peter
3
Jack Table
Rowid
Rowid1
1
1
1
2
1
3
2
1
2
2
3
2
3
3 Table1
Rowid1
Country
1
USA
2
UK
3
JAPAN Table2
I tried to get the Country for all the people in the first table.
My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1
My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ? Thanks in advance ! stephen
Hi there, newbie here. I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server. I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them. Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter. Here's what I want to do: I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created. Here's where I'm up to: I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control. Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along? Cheers!
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records.building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.
scripts (sample):
CREATE TABLE [dbo].[tbl_PersonDetails]( [PersonID] [int] NOT NULL, [LeaveTimeId] [int] NOT NULL ) ON [PRIMARY]
[code]...
Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :
1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails 2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails. 3. not using LEFT join
I am doing some audit and i have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...
I am using SP_Who2 and sql server Audit for auditing all data happening on sql server databases and dumping them to tables Audit_DBAudit abd Audit_sp_who2 and from then i am trying to get data which is not repeating/duplicate ...
SELECT A.ProgramName ,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement FROM Audit_DBAudit as F Join [Audit_sp_who2] AS a on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in. Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]). Any suggestions/comments would be appreciated. Thank you in advance.
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1 EXCEPT SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
Hi all, I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion. Thank U.
I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?
We are running a shopping mall in Korea and got a database including a table of 4 million product prices, which is to be updated hourly basis. Updating 4million records requires at least 10 minutes to complete. During the update, our shopping mall exposed to customers does not respond quickly in fact very very slowly and we investigated and found out that many tables of SQL database during the update were being locked. As you know, site speed is top priority. We studied and found out that there are two ways to avoid having locked tables during update, those are "read uncommitted" and "snapshot" using the following lines.
set transaction Isolation level read uncommitted set transaction Isolation level snapshot
We tried numerous times the above two lines and still find our tables being locked during update and our customers are being disappointed.
My questions:
1. Is it possible at all in view of "the state of the art" to avoid having locked tables during update of 4 million records ?
2. if it is possible, would you please teach me like I am the beginner of database studies?
For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).
i have 2 tables not connected in any way but both have orderid filed (same filed). In one table this filed (and onther one) are keys, the second table dose not hace a key at all. The same order_id CAN repeat itself in each table. When i try to join the tables (some rows just in one table and some in both): Select tab1.name, tab1.orderid, tab2.sku from tab1 inner join tab2 on tab1.orderid=tab2.orderid The result i get is duplicate. each row is multiple. What I'm doing wrong?
select a.[Cuno], [doc_type], [Doc_name] from [assembly] a left join [assembly_Doc] doc ON a.id = doc.assembly_id left join [assembly_Recog] re ON a.id = re.assembly_id where [activate] =1 and doc.doc_type = 1
I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below:
I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.
Use Sales_Builder Go SELECT DISTINCT GBPriceList.[Mfr_Part_Num],
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name, a.cli_first AS First_Name, a.cli_middle AS Mid_Init, b.city AS City, b.county AS County, b.state AS State from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b on a.client_id = b.client_id inner join ECBH.dbo.tbl_client_insurance c on a.client_id = c.client_id inner join ECBH_TEST.dbo.tbl_GEF_County d on b.county = d.COUNTY_NAME where c.plan_id = 4 order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com 1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com 1 2 xyzabc NULL NULL 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL Now, I want unique rows from the above result set like :- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
We are using SQL 2005 to drive a database application. All of the tables are supposed to be owned by user1, so the full table names should be user1.table1, user1.table2, etc.
We have a situation where we have duplicate tables:
This has always been strange, but hasn't really caused many issues.
Part of the application is querying a table called "TOTALS" and the query isn't retunring any data. dbo.TOTALS is empty (since it shouldn't exist) and user.TOTALS has all of our information. Since nothing is being returned by the select query that is just being run against "TOTALS," I would assume it is actually running against dbo.TOTALS.
How can I force SQL to run
SELECT * (actually complex criteria, but * should work for this purpose) FROM TOTALS
to actually run against user1.TOTALS without changing the query since that can't be altered?
Additionally, is there an effecient way to delete all of the dbo owned tables (this database has several hundred tables)?
What I am trying to do is this. I have two tables that someone else created and now I have to fix there mess. Both tables have 3 like fields. I want to check and see if the ProjectNumber field from the Artifacts table does not find a match in the Projects table. If no match is found return the record from artifacts table.
Code:
SELECT * FROM Artifacts INNER JOIN Project ON Artifacts.ProjectNumber = Project.ProjectNumber WHERE Artifacts.ProjectNumber NOT IN (Project.ProjectNumber);
The next thing I have to do is insert a record of the three fields from the Artifacts table to the Projects table. Any help would be great.
Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther
Hello, I have a question, i loaded 2 files into SQL and the files have some cells that have the same model number. how can I merge the cells together that have the same model number and (if possible take the avarage of their cell called price) (and combine their other cell called stock) and make it into one cell. Any help would be very very apriciated. Thank you. i tryed this but it does not work SELECT Model_number FROM Products Join Where Model_number='3CM3C1670800B' I have also Tryed this, IT SHOULD work but I have an error someWhere: delete from Productsfrom part_number a join (select part_number, max(part_number) from part_number group by part_number having count(*) > 1) b on a.part_number = b.part_number and part_number < b.part_number
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14).
-- Add a new column
Alter table dbo.tblMyDocsSize add NewPK int NULL go
-- populate the new Primary Key declare @intCounter int set @intCounter = 0 update dbo.tblMyDocsSize SET @intCounter = NewPK = @intCounter + 1
-- ID the records to delete and get one primary key value also -- We'll delete all but this primary key select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK) into #dupes from dbo.tblMyDocsSize group by strComputer, strATUUser having count(*) > 1 order by count(*) desc, strComputer, strATUUser
-- delete dupes except one Primary key for each dup record deletedbo.tblMyDocsSize fromdbo.tblMyDocsSize a join #dupes d ond.strComputer = a.strComputer andd.strATUUser = a.strATUUser wherea.NewPK not in (select PKtoKeep from #dupes)
-- remove the NewPK column ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK go
drop table #dupes
Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily.
Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE.
So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day?
