473,542 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Selecting ONLY records from one table having ALL data contained in other table (GROUP BY?)

Hello everyone,

Small and (I think) very simple quesiton;-) which makes me creazy.
Let's say I have two tables listed below:

T1
====
IDX
====
1
3
4

T2
===============
IDD fk_IDX
===============
A 1
A 2
A 4
B 1
B 3
B 4
C 4
D 1
D 2
D 3
D 4

I would like to select from table T2 all distinct records IDD which
have all of fk_IDX containded in T1.
The select statement should return in this case ONLY:
B and D
becasue:
B has 1,3,4
and
D has 1,2,3,4 so it has this combination 1,3,4 contained in the T1
also.

I've tried to do that with group by, with having, in and it never
works (I always became all records which one of them is in this T1
table).

Maybe some one from you did try something like that, and can give a
fast answer.

I will be very greatfull

Greatings

Mateusz
Jul 20 '05 #1
2 2315
On 17 Aug 2004 11:13:14 -0700, Matik wrote:
Hello everyone,

Small and (I think) very simple quesiton;-) which makes me creazy.
Let's say I have two tables listed below:
[snip tables]

I would like to select from table T2 all distinct records IDD which
have all of fk_IDX containded in T1.


I think what you want is this technique:

select IDD from T2
inner join T1 on T2.fk_IDX = T1.IDX
group by IDD
having count(T2.fk_IDX ) = (select count(*) from T1)

You should always try to post DDL and sample inserts when you ask questions
here; it makes your problem much clearer and makes it easier for those who
wish to help -- we can just paste into query analyzer and begin.

Below is complete DDL, inserts, your solution and its results, as copied
from query analyzer:

use tempdb

create table T1 (IDX int primary key)
create table T2 (IDD char(1), fk_IDX int,
constraint pk_t2 primary key (IDD, fk_IDX))

insert into T1 (IDX)
select 1 union all select 3 union all select 4

insert into T2 (IDD, fk_IDX) VALUES ('A',1)
insert into T2 (IDD, fk_IDX) VALUES ('A',2)
insert into T2 (IDD, fk_IDX) VALUES ('A',4)
insert into T2 (IDD, fk_IDX) VALUES ('B',1)
insert into T2 (IDD, fk_IDX) VALUES ('B',3)
insert into T2 (IDD, fk_IDX) VALUES ('B',4)
insert into T2 (IDD, fk_IDX) VALUES ('C',4)
insert into T2 (IDD, fk_IDX) VALUES ('D',1)
insert into T2 (IDD, fk_IDX) VALUES ('D',2)
insert into T2 (IDD, fk_IDX) VALUES ('D',3)
insert into T2 (IDD, fk_IDX) VALUES ('D',4)

select IDD from T2
inner join T1 on T2.fk_IDX = T1.IDX
group by IDD
having count(T2.fk_IDX ) = (select count(*) from T1)

IDD
----
B
D

(2 row(s) affected)
Jul 20 '05 #2

Thank you very much for reply, and for information about the rules.
This statement works perfectly.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

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

Similar topics

8
4321
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. Basically I want to say: If fk_ID is in list then do these statements to that record
6
2045
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I need to use parts of the tables in datagrids, and here is where my problem lies
11
3427
by: Ron L | last post by:
I have a data table that lists a series of items in my database. In my user form, I want the user to be able to filter by a number of criteria (e.g. location, contract, date modified, etc). Other than modified date, all my filters are selected via combo boxes. I would like to have the combo boxes update so that if there are no items...
48
3813
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.
24
19866
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every renewal in the history of the policyholder. The information is in 2 tables, policy and customer, which share the custid data. The polno changes...
2
3313
by: Catch_22 | last post by:
Hi, I have a stored procedure that has to extract the child records for particular parent records. The issue is that in some cases I do not want to extract all the child records only a certain number of them. Firstly I identify all the parent records that have the requird number of child records and insert them into the result table.
2
1968
by: larry | last post by:
I am working on a DB for family data, and in this application the data spans variable amount of rows in multiple tables (one for the adults data, one for "family", one for the kids, another for employment schedule, and another for businesses, etc.). I was thinking on selecting the entire family (IDs of all the related records) and storing...
2
6730
by: n4nz | last post by:
I have a table in oracle that contains transaction data by item part number. This means for each part number there will be more than one transaction date, what i am trying to extract is the last transaction date for each part number in a driving table. so.. I have created a table that has 1 column named PART_NO, with numerous part numbers...
4
1733
by: Jeffrey Davis | last post by:
I'm hoping that someone here can give me some assistance with a database I'm trying to set up. My skills in Access are fairly basic, and I'm trying to skill up, but some of the stuff is a little opaque. I'm trying to put some data I've got on paper into Access. Recently, I got together with some other people marketing organic produce and...
0
7392
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
7571
Oralloy
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. ...
0
7670
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...
1
5246
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
4880
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
3380
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...
0
3376
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
943
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
620
bsmnconsultancy
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...

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.