I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table. Hope the info would substitute DDL. I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?
TIA. 12 5153
NickName wrote: I have the following situation. One set of data has 274 rows (set2) and anther has 264 (set1). Both data sets are similar in structure as well as values for both of them were extracts from the same parent table. Hope the info would substitute DDL.
It doesn't.
I need to find the "gap" rows between these two sets. Attempted to run a query like select count(*) from set2 where not exists (select * from set1) did not yield what I desired. What else to try?
Try posting your DDL. We at least need column names and keys to help you
here.
Zach
OK,
I've proven that the "EXISTS" keyword/function can't solve this
problem. But then what?
-- test equality between two data sets
-- desired resultset: return "gap" rows
-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));
insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');
select * into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1
where not exists
(select *
from #tmp2)
NickName (da****@rock.co m) writes: I've proven that the "EXISTS" keyword/function can't solve this problem. But then what?
It certainly can, but you must specify how the NOT EXISTS is to work.
SQL is not about telepathy.
For your repro, you could do
select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t2
WHERE t1.col1 = t2.col1)
or
select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)
depending on what you are looking for. The first just lists missing key
values, the second list all mismatches.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks, Erland.
*) laziness is an enemy, I thought about that but the table has a large
number of columns, so, was wondering if there's another approach. Will
try it tomorrow when the db is available.
*) btw, I like the word, "telepathy" .
Erland Sommarskog wrote: NickName (da****@rock.co m) writes: I've proven that the "EXISTS" keyword/function can't solve this problem. But then what? It certainly can, but you must specify how the NOT EXISTS is to work. SQL is not about telepathy.
For your repro, you could do
select * from #tmp1 t1 where not exists (Select * from #tmp2 t2 WHERE t1.col1 = t2.col1)
or
select * from #tmp1 t1 where not exists (Select * from #tmp2 t WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)
depending on what you are looking for. The first just lists missing
key values, the second list all mismatches.
-- Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
NickName (da****@rock.co m) writes: Thanks, Erland. *) laziness is an enemy, I thought about that but the table has a large number of columns, so, was wondering if there's another approach. Will try it tomorrow when the db is available.
If your aim is to find differences in any column, you will indeed have
to write code that has all column. There is no shortcut. What you can
do, if you have many tables and columns, is to generate code by reading
metadata. But this requires that you have a clear understanding for which
columns you want to compare, and which you do not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland,
Something seems odd. Here's two test cases. Both are successful.
-- test equality between two data sets (with exact meta data)
-- desired resultset: return "gap" rows
-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));
insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');
select * into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)
-- outcome = success
-- test equality between two data sets
-- with minor meta data difference (one table has 3 attributes while
the other has 2)
-- note: comparison seems still successful
-- desired resultset: return "gap" rows
drop table #tmp1
drop table #tmp2;
-- DDL and DML
create table #tmp1 (col1 int, col2 char(1),col3 int);
insert into #tmp1
values(1,'a',11 )
insert into #tmp1
values(2,'b',22 )
insert into #tmp1
values(3,'c',33 )
insert into #tmp1
values(4,'d',44 )
insert into #tmp1
values(5,'e',55 );
select col1,col2 into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)
-- outcome = success
HOWEVER, when I applied the above to my tables (sorry I can't post
exact structure nor data here, PARENT table has 13 columns and the
derived table has 12 of them, comparison is between them), sql still
failed to find the "gap". What could possibly stands in the way?
Many thanks.
Ahe, I think I've found the problem. In "my" tables, both has
duplicate rows, however, the number of duplicate rows are not the same,
(distinct rows are sure the same).
NickName (da****@rock.co m) writes: Ahe, I think I've found the problem. In "my" tables, both has duplicate rows, however, the number of duplicate rows are not the same, (distinct rows are sure the same).
I'm not sure how this should be addressed. Are you saying that in
one table you have two rows with the same value, but in another you
only have one?
If there are no disctinct keys in the data, all relational operations will
be problematic, that's for sure.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
You might find it easier to export the table contents as ordered data and
then use a text file compare utility. Command-prompt example:
BCP "SELECT * FROM MyDatabase..Tab le1 ORDER BY MyPK" queryout
"C:\temp\Table1 .txt" /T /c /SMyServer
BCP "SELECT * FROM MyDatabase..Tab le2 ORDER BY MyPK" queryout
"C:\temp\Table2 .txt" /T /c /SMyServer
WINDIFF "C:\temp\Table1 .txt" "C:\temp\Table2 .txt"
--
Happy Holidays
Dan Guzman
SQL Server MVP
"NickName" <da****@rock.co m> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Ahe, I think I've found the problem. In "my" tables, both has duplicate rows, however, the number of duplicate rows are not the same, (distinct rows are sure the same). This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan Stromberg |
last post by:
We will soon have 3 copies, for testing purposes, of what should be about
4.5 terrabytes of data.
Rather than cmp'ing twice, to verify data integrity, I was thinking we
could speed up the comparison a bit, by using a python script that does 3
reads, instead of 4 reads, per disk block - with a sufficiently large
blocksize, of course.
My question then is, does python have a high-level API that would
facilitate this sort of thing, or...
|
by: Skip Montanaro |
last post by:
I'd like to compare two xml.dom.minidom objects, but the naive attempt fails:
>>> import xml.dom.minidom
>>> d1 = xml.dom.minidom.parse("ES.xml")
>>> d2 = xml.dom.minidom.parse("ES.xml")
>>> d1 == d2
False
My goal is to decide whether or not I need to prompt the user to save config
information at the end of a program run by generating a minidom object then
|
by: William Park |
last post by:
How do you compare 2 strings, and determine how much they are "close" to
each other? Eg.
aqwerty
qwertyb
are similar to each other, except for first/last char. But, how do I
quantify that?
I guess you can say for the above 2 strings that
- at max, 6 chars out of 7 are same sequence --> 85% max
|
by: Odd-R. |
last post by:
I have to lists, A and B, that may, or may not be equal. If they are not
identical, I want the output to be three new lists, X,Y and Z where X has
all the elements that are in A, but not in B, and Y contains all the
elements that are B but not in A. Z will then have the elements that are
in both A and B.
One way of doing this is of course to iterate throug the lists and compare
each of the element, but is there a more efficient way?
...
|
by: Dennis |
last post by:
I have a public variable in a class of type color declared as follows:
public mycolor as color = color.Empty
I want to check to see if the user has specified a color like;
if mycolor = Color.Empty then.....
or
if mycolor is Color.Empty then .......
| |
by: John Salerno |
last post by:
I'd like to compare the values in two different sets to test if any of
the positions in either set share the same value (e.g., if the third
element of each set is an 'a', then the test fails).
I have this:
def test_sets(original_set, trans_letters):
for pair in zip(original_set, trans_letters):
if pair == pair:
return False
|
by: Yash |
last post by:
Hi,
We are in the process of tuning the performance of our stored procs in
SQL 2000 and are looking for a tool that would help us in comparing
the result sets of an old SP and a modified SP. The tool should take
as input the names of the 2 SPs, a set of parameter values and invoke
them. Internally it should compare the result sets returned and log/
display the difference in result sets if any.
We do not want to compare response time,...
|
by: DomiNeug |
last post by:
Hello,
Since a while i have to find a way of comparing "Sets" (multiple int Values) and so to find equal sets. There simply 3 tables
ValueList with: ID int
ValueListHasValue: ID int, ValueListID int, ValueID int
Value: ID int, Description varchar(max)
The sets are defined in the ValueList has ValueTable. Any combination and order is possible as there is also no restriction on the amount of Values in a ValueList. My first approach was to...
|
by: Sean Dalton |
last post by:
Hello,
I have a two sets OLDLIST and REMOVE.
I would like to remove every element in OLDLIST if it is also occuring
in REMOVE
and store the remaining elements from OLDLIST into NEWLIST.
So far, I have read in both lists but I'm struggling comparing the
elements
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |