473,574 Members | 2,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need to find instances of duplicates within a column; joining 2 tables.

My basic situation is this - I ONLY want duplicates, so the opposite
of DISTINCT:

I have two tables. Ordinarily, Table1ColumnA corresponds in a one to
one ratio with Table2ColumnB through a shared variable. So if I query
TableB using the shared variable, there really should only be on
record returned. In essence, if I run this and return TWO rows, it is
very bad:

select * from TableB where SharedVariable = 1234

I know how to join the tables on a single record to see if this is the
case with one record, but I need to find out how many, among possibly
millions of records this affects.

Every record in Table1ColumnA (and also the shared variable) will be
unique. There is another column in Table1 (I'll call it
Table1ColumnC) that will be duplicated if the record in Table2 is a
duplicate, so I am trying to use that to filter my results in Table1.
I am looking to see how many from Table1 map to DUPLICATE instances in
Table2.

I need to be able to say, in effect, "how many unique records in
Table1ColumnA that have a duplicate in Table1ColumnC also have a
duplicate in Table2ColumnB?"

Thanks if anyone can help!

-- aknoch

Aug 21 '07 #1
1 2477
I'm not sure if you can simply test each table individually or if you
have to join them, but the basic approach is the same either way.
>select * from TableB where SharedVariable = 1234
The query to test TableB alone and find ALL values of SharedVariable
that appear more than once:

SELECT SharedVariable, count(*) as Dups
FROM TableB
GROUP BY SharedVariable
HAVING count(*) 1

If you need to see all the data in the rows involved then use the
query above to determine the set of values to search for:

SELECT TableB.*
FROM TableB
JOIN (SELECT SharedVariable, count(*) as Dups
FROM TableB
GROUP BY SharedVariable
HAVING count(*) 1) as K
ON TableB.SharedVa riable = K.SharedVariabl e

I hope that helps.

Roy Harvey
Beacon Falls, CT

On Tue, 21 Aug 2007 16:33:31 -0700, ak****@gmail.co m wrote:
>My basic situation is this - I ONLY want duplicates, so the opposite
of DISTINCT:

I have two tables. Ordinarily, Table1ColumnA corresponds in a one to
one ratio with Table2ColumnB through a shared variable. So if I query
TableB using the shared variable, there really should only be on
record returned. In essence, if I run this and return TWO rows, it is
very bad:

select * from TableB where SharedVariable = 1234

I know how to join the tables on a single record to see if this is the
case with one record, but I need to find out how many, among possibly
millions of records this affects.

Every record in Table1ColumnA (and also the shared variable) will be
unique. There is another column in Table1 (I'll call it
Table1Column C) that will be duplicated if the record in Table2 is a
duplicate, so I am trying to use that to filter my results in Table1.
I am looking to see how many from Table1 map to DUPLICATE instances in
Table2.

I need to be able to say, in effect, "how many unique records in
Table1Column A that have a duplicate in Table1ColumnC also have a
duplicate in Table2ColumnB?"

Thanks if anyone can help!

-- aknoch
Aug 22 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3057
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -----Urspr=FCngliche...
3
6409
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName, Employees.LastName, TerritoryID, Employees.EmployeeID, RegionID, ProductID from Employees
2
1838
by: Dennis Gearon | last post by:
IPU, in place updates. -------------------------- 1/ Put a version numbers on each record represented by a byte/word sized number from a three version number list kept by the table header. This would then only involve one more byte per record. This version number list needs to be the VERY first item in the table header. The lowest ordered...
3
4593
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: tblItems ItemID ItemLabel
2
3381
by: M.Stanley | last post by:
Hi, I have a problem..I'm doing a specific query where I'm joining fields from a table with appednded data (there are duplicate records, except for the date/time), and another query. I want the results of the new query to return ALL of the matches, and only the latest record when it finds duplicates. Performing a return top values doesn't...
48
3829
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a query of all students in both tables with no duplicates. No clue whatsoever.
16
4166
by: tyrfboard | last post by:
I've been searching for awhile now on how to remove duplicates from a table within an Access db and have found plenty of articles on finding or deleting duplicates. All I want to do is remove them from within an SQL query - leaving one of the records behind of course. I have a mailing list comprised of a union query that gets records from...
4
7078
by: yin_n_yang74 | last post by:
I am new to SQL and SQL Server world. There must be a simple solution to this, but I'm not seeing it. I am trying to create a crystal report (v8.5) using a stored procedure from SQL Server (v2000) in order to report from two databases and to enable parameters. When I create the stored procedure, it joins multiple one-to-many relationship...
1
2150
by: tskmjk55 | last post by:
Recently, I have a requirement to develop a vb.net application wherein the input excel sheet data which has an average of 5000 records should be checked for Internal duplicates (duplicates within the same sheet) and external duplicates (duplicates which exist outside this sheet). I have gone through lot of logics..some of which are ... -...
0
7807
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7820
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8100
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6456
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5624
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5301
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1344
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.