Re: [Q, theory] how to write complicated query?
From: Igor M. Klinchin <karmen_at_whale.sunbay.crimea.ua>
Date: 1996/09/16
Message-ID: <51j643$g4n_at_ark.cris.crimea.ua>#1/1
: >Hi, folks!
: >
: >We have one problem and not understand now is this an our problem
: >or the real problem. So two tables
: >
: >Master
: >------
: >pkey,
: >name
: >
: >Detail
: >------
: >pkey,
: >fkey_master references master(pkey),
: >num number -- some positive integer
: >
: >We need select all pkey's from Master grouping them by
: >name
: >and equal sets of Detail.num.
: >
: >We are interested not only with particular solution using
: >native oracle methods but with solution using standard SQL.
: >
: >With best wishes,
: >
: >dk
: >
Date: 1996/09/16
Message-ID: <51j643$g4n_at_ark.cris.crimea.ua>#1/1
Steve Dodsworth (Steven_Dodsworth_at_qsp.co.uk) wrote:
: In <516j34$da8_at_ark.cris.crimea.ua>, karmen_at_whale.sunbay.crimea.ua (Igor M. Klinchin) writes:
: >Hi, folks!
: >
: >We have one problem and not understand now is this an our problem
: >or the real problem. So two tables
: >
: >Master
: >------
: >pkey,
: >name
: >
: >Detail
: >------
: >pkey,
: >fkey_master references master(pkey),
: >num number -- some positive integer
: >
: >We need select all pkey's from Master grouping them by
: >name
: >and equal sets of Detail.num.
: >
: >We are interested not only with particular solution using
: >native oracle methods but with solution using standard SQL.
: >
: >With best wishes,
: >
: >dk
: >
: I'm not quite sure what you require, could you post some small data and : required output examples
Well. So lets Master contains follow records
PKEY NAME 1 name 2 name 3 name 4 name 5 name 6 name And Detail contains PKEY FKEY_MASTER NUM 1 1 1 2 1 2 3 2 1 4 3 1 5 3 2 6 4 1 7 4 2 8 4 3 9 6 1 10 6 2 11 6 3
So
master PKEY=1 has name 'name' and set of detail NUMs (1,2) master PKEY=2 has name 'name' and set of detail NUMs (1) master PKEY=3 has name 'name' and set of detail NUMs (1,2) master PKEY=4 has name 'name' and set of detail NUMs (1,2,3) master PKEY=5 has name 'name' and set of detail NUMs () master PKEY=6 has name 'name' and set of detail NUMs (1,2,3)
I need the follow result
MASTER.PKEY
1
2
4
5
Because I really have four groups of MASTER.PKEYs
MASTER.PKEYs NAME set of NUMs 1,3 name (1,2) 2 name (1) 4,6 name (1,2,3) 5 name () null set
I need to select f.e. min(master.pkey) from every group. I not need select PKEYs for certain set of NUMs. I need grouping and selecting one member from ALL groups.
Thanks for your interest,
dk Received on Mon Sep 16 1996 - 00:00:00 CEST