Checking duplicate values two tables


Hi ,

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.

Please Advise.
Vijay

Posted On: Friday 26th of October 2012 12:04:23 AM Total Views:  375
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




removing duplicates from inner joins

Hi There, I was wondering if someone could help me with the results on this query, at the moment I am getting values repeated and I was wondering if it was possible to have some of the columns grouped, I have tried to have grouping at the end of the query but this still did not group the rows.
VIEWS ON THIS POST

132

Posted on:

Friday 26th October 2012
View Replies!

stopping duplicates

I have got the statment below but it pulls out duplicates, because maybe more than one call has been made, anyone know how i can stop this SELECT dbo.tblContactLog.CallBackEmployee_id, dbo.tblLeads.surname FROM dbo.tblLeads INNER JOIN dbo.tblContactLog ON dbo.tblLeads.lead_id = dbo.tblContactLog.lead_id WHERE (dbo.tblContactLog.CallBackEmployee_id = 12)
VIEWS ON THIS POST

114

Posted on:

Friday 26th October 2012
View Replies!

Create view that checkfor duplicated column data per same key id

i 've table called tbl1 with data as follow : Tbl1 name time1 time2 john 07:00 09:30 john 07:00 17:34 Jimmy 08:00 15:36 how to check time1 if duplicated for the same name , put a stat "" at the column "stat" so the result view is like this: name time1 time2 stat john 07:00 09:30 john 07:00 17:34 Jimmy 08:00 15:36
VIEWS ON THIS POST

200

Posted on:

Wednesday 7th November 2012
View Replies!

Find duplicates

I have a field called user_no i want to find out which ones are duplicates in the user_no field the data in user_no is like this 111-222-345-666 so there are 10,000 records in the table and i want to find out the duplicate records in them can someone tell me how my query will be todd
VIEWS ON THIS POST

110

Posted on:

Saturday 10th November 2012
View Replies!

duplicate records

I'm having trouble working out why this select statement is returning duplicate values For example. What should be returned is: c_Name c_UserMeetingId Peter Murtha 1178 Ian Grey-Wilson 1179 But what I get is: c_Name c_UserMeetingId Peter Murtha 1178 Peter Murtha 1179 Ian Grey-Wilson 1178 Ian Grey-Wilson 1179 The select statement I'm using is this: SELECT u.c_Name, um.c_UserMeetingId FROM t_User u, t_ThirdParty tp, t_UserMeeting um WHERE u.c_EmployeeId = tp.c_AppraiseeEmployeeId AND tp.c_EmployeeId = 'K005410' AND tp.c_EmployeeId = um.c_ThirdPartyEmployeeId AND tp.c_Accepted = 1 K005410 being who is checking to see what results are against their EmployeeId Any thoughts Cheers
VIEWS ON THIS POST

127

Posted on:

Saturday 10th November 2012
View Replies!

How to trap error for duplicate key

With VFP, I have created a Form based on remote view of a table in MS SQL Database, that have a primary key. If the record is new, after I have enter the primary key, I need to control if the code exist already. I have not problem with the dbf file, but I have some difficulty with SQL database. Someone can help me, in order to resolve this problem Thank you. Best
VIEWS ON THIS POST

137

Posted on:

Saturday 10th November 2012
View Replies!

Not show duplicate entries

Hi all, i have a qwik question, here i have a query to display all the records from the database 'counter' Code: SELECT * FROM dbo.counter In this table, there is a column called 'USERIP'. Within this column, some IP addresses can be shown more than once. How would i go about displaying all the records from the table and not show the duplicate USERIP fields (only display one for each IP address) Basically, this is a hit counter for a page, and i only want to display unique IP's/hits.
VIEWS ON THIS POST

217

Posted on:

Sunday 11th November 2012
View Replies!

delete duplicate records

hi..... is it possible\t company_id is identity coulmn..so both rows has unique value in this fields..in company_name field both rows have same value(means duplicate values)...other fields may or may not has same values...so can we write a such query which is base on company_name field and we delete duplicates record...............thanx DELETE FROM company_master WHERE (company_id NOT IN (SELECT MIN(company_id) FROM company_master GROUP BY company_id, company_name, catalogue, website, login, password, logo, printad, subscription_date, expiry_date, record_date, update_date, userid, approved, catalogproduct_count, organisation_description))
VIEWS ON THIS POST

139

Posted on:

Sunday 11th November 2012
View Replies!

find duplicates problem

good day to all mods. how can i fix this query to find duplicates on my table and count how many dumplicates each names. this is my query and has error: Code: SELECT * FROM (SELECT personality_nam_last || personality_nam_first || personality_nam_middle AS "NAMES" FROM personalities) AS foo WHERE count(*) > 1; error: Code: Aggregates not allowed in WHERE clause
VIEWS ON THIS POST

203

Posted on:

Sunday 11th November 2012
View Replies!

Problems with slight differences in two 'duplicate' records

I am having a tricky problem. I have records in a database that are in pairs based on a composite key (3 fields). Some of the pairs are completely identical, meaning each and every field in those pairs are the same. Some, however have a slight difference, in that one of the fields was updated and does not make the pairs truly identical. Let's assume a table like this: Table Name - FOOD Fields: (RecID, Name, Type, Color, Size) And the table structure is laid out like so: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 2, Grape, Fruit, Purple, Small 2, Grape, Fruit, Purple, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large What I want to do is write a query that will show me only the pairs that have changes in them. When I run the query I would want to see the following: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large I would not want to see #2 (Grapes) because the records are identical. Might I have to use a self join
VIEWS ON THIS POST

177

Posted on:

Sunday 11th November 2012
View Replies!

DropDown Menu - Stop duplicate values showing

I have got a drop down menu that displays everything in my "topic" field in access database. Does anyone know how I can make the values in the dropdown box single values. Eg if you had a car colour field "Red" may come up 10 times but how can you get it to appear once in the dropdown menuHelp would be appreciated Rob
VIEWS ON THIS POST

187

Posted on:

Sunday 11th November 2012
View Replies!

Deleting duplicate records

I have many duplicate records in a table. I want to keep one record out of it. and delete the remaining records. This table has one ID field. which is identity field. sample data: ID Name 1 AA 2 AB 3 AA 4 AB 5 AA 6 DD 7 AB Result should be: Name AA AB DD ID can be anything. How can do it I want to do it in only single SQL Statement.
VIEWS ON THIS POST

138

Posted on:

Thursday 15th November 2012
View Replies!

Delete duplicates - T/SQL

Can anyone help please I have some code which gets a list of duplicate records. I want to be able to delete one of the records and leave the other. However so far my code deletes all records. Can anyone tell me how I select just one of each pair to delete It doesn't matter which one of the pair goes. delete FROM Sickness WHERE (staff_id IN (SELECT [staff_id] FROM [Sickness] AS Tmp GROUP BY [staff_id], [start_date], [end_date] HAVING COUNT(*) > 1 AND [start_date] = [Sickness].[start_date] AND [end_date] = [Sickness].[end_date])) Thank you!
VIEWS ON THIS POST

120

Posted on:

Thursday 15th November 2012
View Replies!

Case when is causing duplicates

Hi , I'm self taught in SQL and have completely hit a wall, please help! I'm trying to use the 'Case when' function to flag any students that have arrived late as they have a particular condition entered against their record (in the dfcond table). The problem is that they also have many other conditions so I'm getting duplicates that I dont want! Here is my query: SELECT df.dfkey, df.status, cq.cqtitle, dfpath.course, dfpath.weeks, df.ELICOS_rate, student_start, student_end, sem_start, sem_end, dfpath.weeks*ELICOS_Rate AS Total_Inv_Due, (Select SUM(tramt) from dff where df.dfkey=dff.code and trperiod between dfpath.sem_start and dfpath.sem_end and trtype='I') AS Total_Inv_To_Date, Case when enr_cond ='LA1' then 'Late X 1' when enr_cond ='LA2' then 'Late X 2' when enr_cond ='LA3' then 'Late X 3' when enr_cond ='LA4' then 'Late X 4' when enr_cond ='LA5' then 'Late X 5' else '' end as 'Late condition' FROM df join dfpath on df.dfkey=dfpath.skey JOIN dfcond on df.dfkey=dfcond.skey JOIN dff on dfpath.skey=dff.code JOIN kc on dfpath.course=kc.course JOIN cq on df.scagent=cq.cqkey WHERE dfpath.student_start>=[From start date] AND dfpath.student_start
VIEWS ON THIS POST

123

Posted on:

Tuesday 20th November 2012
View Replies!

SQL server query - Merging duplicate rows

Code: SELECT starttime AS Time_Period, SUM(incalls) AS [Inbound Calls], SUM(acdcalls) AS [ACD Calls], SUM(abncalls) AS [Abandoned Calls], CASE WHEN SUM(incalls) = 0 THEN 0 ELSE 100 * SUM(abncalls) / SUM(incalls) END AS [% Abandoned], CASE WHEN SUM(acdcalls) = 0 THEN 0 ELSE (SUM(anstime) / SUM(acdcalls)) END AS [Avg Speed Answer], CASE WHEN SUM(abntime) = 0 THEN 0 ELSE (SUM(abntime) / SUM(abncalls)) END AS [Avg Abandoned Time], CASE WHEN SUM(incalls) = 0 THEN 0 ELSE ((SUM(ansconncalls1) + SUM(ansconncalls2) + SUM(ansconncalls3) + SUM(ansconncalls4)) / SUM(incalls) * 100) END AS [% Calls Ans in 30 Sec], SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6) AS [Aban calls in 30s], CASE WHEN SUM(incalls) = 0 THEN 0 ELSE ((SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)) / SUM(incalls) * 100) END AS [% Aban in 30s] FROM hvdn WHERE (acd = 1) AND (row_date = CONVERT(DATETIME, '2009-07-23 00:00:00', 102)) GROUP BY starttime ORDER BY Time_Period The above code DOES work and produces the expected results. A summary of the data returned is displayed below: Image link (http://dayoaudi.co.uk/results.jpg) My problem is the "similar/duplicate" row at 1000. I need the respective values (while taking into account the existing calculations performed in the query) merged. The deed result is that each half-hourly time period has a single row of data each. If anyone can help me, I would really, really appreciate it. [] I have posted this query on a few discussion forums about SQL so if/when I get an answer, I will update this post accordingly. I am using SQL Server 2005 Enterprise.
VIEWS ON THIS POST

237

Posted on:

Tuesday 20th November 2012
View Replies!

Stored proc : updating /checking dates

hi, I want to modify a stored procedure to check for certain dates. I check for Null values, but I never get any data, even though the fields do say "Null" inside the table. I used '' which works on one of the date fields. I am not sure what I am doing wrong. Can somebody tell me what might be wrong The two fields that I am checking are submitted and validated, both Datetime fields. I also wanted to return the number of counts to see what records I got from this last statement. Is that correct - Thank you.. Code: ALTER PROCEDURE dbo.SPSubmitTS ( @TSKey iNT , @subDate datetime , @inputDate1 datetime, @inputDate2 datetime, @countSub integer output ) AS BEGIN UPDATE TimeData SET submitted = @subDate where tkey = @tskey AND (CONVERT(CHAR(10),TDATE,111 )>= convert(char(10),@inputDate1 ,111) and CONVERT(CHAR(10),TDATE,111 )
VIEWS ON THIS POST

146

Posted on:

Tuesday 20th November 2012
View Replies!

Check for duplicate data before insert

I am currently doing an insert based on a selection from another table in a query within DTS. Before I do the insert I will like to check to make sure that the data is not already in the database. I want to do this check based on 2 fields. Does anyone have a good solution in achieving this
VIEWS ON THIS POST

154

Posted on:

Tuesday 20th November 2012
View Replies!

How do i display duplicate records

Hi , Iam running procedure 'spr_example' from sql server management studio and iam using same procedure in the reporting services.But running same procedures in ssms and ssrs giving different results.I have found the problem,in management studio its displaying duplicate records but in reporting services its not including duplicate data. Can anyone suggest me how to display duplicate data in reporting services..
VIEWS ON THIS POST

319

Posted on:

Monday 26th November 2012
View Replies!

error checking in ASP

hi !! i have another problem in my coding... how can i add an error checking in my page\t because i have a customer registration page where a customer will input items the page... how can i have an error checking in the page, just like having an error like "username should not be more than 12 characters" or to have an error checking account like "the password you have entered is not the same" because there will be a field with "password" and "re-type password..."
VIEWS ON THIS POST

185

Posted on:

Monday 26th November 2012
View Replies!

How to trap error for duplicate key

With VFP, I have created a Form based on remote view of a table in MS SQL Database, that have a primary key. If the record is new, after I have enter the primary key, I need to control if the code exist already. I have not problem with the dbf file, but I have some difficulty with SQL database. Someone can help me, in order to resolve this problem Thank you. Best
VIEWS ON THIS POST

196

Posted on:

Monday 26th November 2012
View Replies!