I am doing a contract job for a client with
some existing messy data.
I am trying to find a solution to a problem of
many-to-many mappings between two tables in
a database. See below for my contrived example.
Tables a and b each have simple primary keys.
They also have 2 columns that are related to
each other. In other words, a1 and b1 carry the
same "key" type of data. Same thing with a2 and
b2. I need to locate all of the "groups" of records
in both table a and table b where there is
more than one record in both tables. Man, this is
even difficult to state...
DROP DATABASE IF EXISTS many;
CREATE DATABASE many;
USE many;
CREATE TABLE a
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a1 INT NOT NULL,
INDEX(a1),
a2 INT NOT NULL,
INDEX(a2)
);
CREATE TABLE b
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b1 INT NOT NULL,
INDEX(a1),
b2 INT NOT NULL,
INDEX(a2)
);
INSERT INTO a (a1,a2) VALUES (1,8);
INSERT INTO a (a1,a2) VALUES (1,2);
INSERT INTO a (a1,a2) VALUES (2,1);
INSERT INTO a (a1,a2) VALUES (1,8);
INSERT INTO a (a1,a2) VALUES (1,1);
INSERT INTO a (a1,a2) VALUES (1,1);
INSERT INTO a (a1,a2) VALUES (4,4);
INSERT INTO a (a1,a2) VALUES (1,2);
INSERT INTO a (a1,a2) VALUES (1,2);
INSERT INTO b (b1,b2) VALUES (1,2);
INSERT INTO b (b1,b2) VALUES (2,1);
INSERT INTO b (b1,b2) VALUES (1,8);
INSERT INTO b (b1,b2) VALUES (1,8);
INSERT INTO b (b1,b2) VALUES (3,3);
INSERT INTO b (b1,b2) VALUES (1,2);
OK, so in my contrived example, I would like to
retrieve groups as follows:
group: table a: #1, #4; table b: #3, #4;
group: table a: #2, #8, #9; table b: #1, #6;
Hopefully, you follow my notation.
Is there a way to do this in straight SQL? If not,
how would you do it? The tables are huge, with 10's
of millions of records in each table.
Thanks in advance.
Dean Hoover 0 1336 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bo Peng |
last post by:
Dear Python group:
I am planning on an application that involves several complicated C++
classes. Basically, there will be one or two big data objects and some
"action" objects that can act on the data. I would like to use a script
language to control the interaction between these c++ objects.
I become interested in Python since it can load C++ objects and can even
extend C++ classes. However, I am not quite sure to what extent can...
|
by: Agoston Bejo |
last post by:
Hi, I'm having trouble with implementing some constraints on the database
level. An example:
--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)
upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.
|
by: Ulrich Albrecht |
last post by:
Hi,
I'm making a complicated css based site (CMS) without tables (because
of Accessibility).
It looks good in any new browsers on my machine (IE5.5, IE6, NS7, OP6,
Mozilla 1.1) :-)
You can zoom and you can resize the window.
But with browsercam.com it was possible to see (8 hours) my page
through other different browsers, like AOL, older NS and the new
|
by: Tom Schmitt |
last post by:
I tried getting a complicated, multi-column box design on it's way on
my web-site and it works out fine on the IE6. Because I'm a relative
beginner to all stuff related to CSS and am used to workung with
tables to achieve the results I want, I'm still baffled by how easy
and good looking CSS-Layout can be, in code and the on screen results.
I will surely never use tables again...
But here's my problem: If you look at the site
...
|
by: Johm |
last post by:
I have a very complicated problem.I have a function that checks
whether the cartons in the warehouse,which are branch0, are greater
than the cartons ordered. Which means that
If branch < cartons then
exit function
The function works great if the cartons are not opened.But when one
carton is opened, and one piece from the carton is sold, then the
number of the cartons in the warehouse is with 1 less, since one
carton
| |
by: Jeff |
last post by:
Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2 tables I
will be using "username" and "points"
Now, I also have a table called all_matches. This table contains every match
report. Over 25,000 of them. I have a "username" field an "outcome" field an
"username1" field and "extra_match" field.
|
by: Henrry Pires |
last post by:
Hello to all.
I have and strange and complicated problem.
My asp.net application raise errors when the data that the user input is
invalid. For example: The user didn't fill all some data (customer name or
SSN and so on). I have a class that try to validate the data and is
something is wrong it raises an error. That error is a class that I made,
|
by: Charlie Bear |
last post by:
i've got myself into a bit of an oo mess. it's probably me
misunderstanding how oo works.
I've got a base class called "Feature" which some classes inherit. in
the database i've stored the data in a table along with a type id to
tell me what type of feature it is. ie a poll feature has an id of two
and has the properties of feature plus a poll id which links to a poll
table. the poll feature has seperate methods that the base feature...
|
by: CoreyWhite |
last post by:
So I'm reading books about perl, which may not be quite as powerful as
C++ but at least has more power than C & is very easy to learn from
the manuals. I'm also buying books on C++, and books about algorithms
for C. It isn't easy to learn it though, and I have been trying to
learn unix, linux, and C++ since day 1. Even when I first turned on a
windows machine I fantasized about running my own unix server. Now I
pay a good rate every...
|
by: Horacius ReX |
last post by:
Hi,
I have to read some data from a file, and on each block it always
appears the followng string; xyz.vs.1-81_1 . It appears a lot of time
with different numbers like;
xyz.vs.1-81_1
xyz.vs.1-1234_1
xyz.vs.1-56431_1
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |