group by query with join - problem

group by query with join - problem

Post by T. Marqua » Sun, 28 Apr 2002 21:56:58



Hello you gurus!

I am working on a complex query and cannot figure out how it works.

Imagine tree tables (sample-code below)

1. holding the super-entities
2. holding categories with a priority-order (in real world this is not
just one table but for simplicity)
3. holding the entities: each entity has a parent sup_entity and a
category

I want the query to give me all the entities whith the
min(priority-order) gruped by the super-entities
in other words: give me for each super_entity the (sub)-entity with
the highest category-priority value.
Rhe result must not have more rows than entries in the super_entity
table.

I tried something like
select e_s_id, min(cat_prio) from sub_entities inner join category on
e_cat_id = cat_id group by e_s_id
or
select e_id, min(cat_prio) from sub_entities inner join category on
e_cat_id = cat_id group by e_id

the solution sould be somewhere in the middle

BUT THE PROBLEM is that i cannot SELECT e_id here without grouping
e_id
according to my example-code (see below) i want to have exactly this
result

e_id
--------------
1
2
6
7
8
9
11
12

do you have an idea??

Thanks very much, Tobias

-----------------------code ------------------------
use pubs

create table sup_ent (s_id int not null primary key)

create table category(cat_id int not null primary key,
cat_prio int not null)

create table sub_entities (e_id int not null primary key,
e_s_id int not null references sup_ent(s_id) ,
e_cat_id int not null references category(cat_id))

insert into category values (1,3)
insert into category values (2,1)
insert into category values (3,2)
insert into category values (4,5)
insert into category values (5,4)

insert into sup_ent values (1)
insert into sup_ent values (2)
insert into sup_ent values (3)
insert into sup_ent values (4)
insert into sup_ent values (5)
insert into sup_ent values (6)
insert into sup_ent values (7)
insert into sup_ent values (8)

insert into sub_entities values (1,1,2)
insert into sub_entities values (2,2,1)
insert into sub_entities values (3,2,4)
insert into sub_entities values (4,2,5)
insert into sub_entities values (5,3,3)
insert into sub_entities values (6,3,2)
insert into sub_entities values (7,4,1)
insert into sub_entities values (8,5,2)
insert into sub_entities values (9,6,2)
insert into sub_entities values (10,7,4)
insert into sub_entities values (11,7,1)
insert into sub_entities values (12,8,2)
insert into sub_entities values (13,8,3)

select e_id, min(cat_prio) from sub_entities
        inner join category on e_cat_id = cat_id group by e_id
select e_s_id, min(cat_prio) from sub_entities
inner join category on e_cat_id = cat_id group by e_s_id

--------------------
drop table sub_entities
drop table sup_ent
drop table category

 
 
 

group by query with join - problem

Post by Darren Brinksneade » Mon, 29 Apr 2002 02:08:24


Here is one way...

SELECT e_id
FROM sub_entities SE1 INNER JOIN category CA on e_cat_id = cat_id INNER JOIN
(select e_s_id, min(cat_prio) as HighestPriority
                                 from sub_entities inner join category on
e_cat_id = cat_id
                                 group by e_s_id) As SE2
     ON SE1.e_s_id = SE2.e_s_id AND CA.cat_prio = SE2.HighestPriority

Result:
e_id
-----------
1
2
6
7
8
9
11
12

--
Darren Brinksneader
MCDBA, MCSE, CCDA, MCT, CTT+, CCA, CNE
Chief Technologist
A Technological Advantage, Inc.


Quote:> Hello you gurus!

> I am working on a complex query and cannot figure out how it works.

> Imagine tree tables (sample-code below)

> 1. holding the super-entities
> 2. holding categories with a priority-order (in real world this is not
> just one table but for simplicity)
> 3. holding the entities: each entity has a parent sup_entity and a
> category

> I want the query to give me all the entities whith the
> min(priority-order) gruped by the super-entities
> in other words: give me for each super_entity the (sub)-entity with
> the highest category-priority value.
> Rhe result must not have more rows than entries in the super_entity
> table.

> I tried something like
> select e_s_id, min(cat_prio) from sub_entities inner join category on
> e_cat_id = cat_id group by e_s_id
> or
> select e_id, min(cat_prio) from sub_entities inner join category on
> e_cat_id = cat_id group by e_id

> the solution sould be somewhere in the middle

> BUT THE PROBLEM is that i cannot SELECT e_id here without grouping
> e_id
> according to my example-code (see below) i want to have exactly this
> result

> e_id
> --------------
> 1
> 2
> 6
> 7
> 8
> 9
> 11
> 12

> do you have an idea??

> Thanks very much, Tobias

> -----------------------code ------------------------
> use pubs

> create table sup_ent (s_id int not null primary key)

> create table category(cat_id int not null primary key,
> cat_prio int not null)

> create table sub_entities (e_id int not null primary key,
> e_s_id int not null references sup_ent(s_id) ,
> e_cat_id int not null references category(cat_id))

> insert into category values (1,3)
> insert into category values (2,1)
> insert into category values (3,2)
> insert into category values (4,5)
> insert into category values (5,4)

> insert into sup_ent values (1)
> insert into sup_ent values (2)
> insert into sup_ent values (3)
> insert into sup_ent values (4)
> insert into sup_ent values (5)
> insert into sup_ent values (6)
> insert into sup_ent values (7)
> insert into sup_ent values (8)

> insert into sub_entities values (1,1,2)
> insert into sub_entities values (2,2,1)
> insert into sub_entities values (3,2,4)
> insert into sub_entities values (4,2,5)
> insert into sub_entities values (5,3,3)
> insert into sub_entities values (6,3,2)
> insert into sub_entities values (7,4,1)
> insert into sub_entities values (8,5,2)
> insert into sub_entities values (9,6,2)
> insert into sub_entities values (10,7,4)
> insert into sub_entities values (11,7,1)
> insert into sub_entities values (12,8,2)
> insert into sub_entities values (13,8,3)

> select e_id, min(cat_prio) from sub_entities
> inner join category on e_cat_id = cat_id group by e_id
> select e_s_id, min(cat_prio) from sub_entities
> inner join category on e_cat_id = cat_id group by e_s_id

> --------------------
> drop table sub_entities
> drop table sup_ent
> drop table category


 
 
 

group by query with join - problem

Post by lindawi » Mon, 29 Apr 2002 02:27:47


Tobias,

Thanks for the DDL. :)

How about something like this:

select e_id
from   sub_entities
join    category
on   sub_entities.e_cat_id = category.cat_id
join   (select  e_s_id, min(cat_prio) cat_prio
        from    sub_entities
        join    category
        on      e_cat_id = cat_id
        group by e_s_id
) minprio
on      minprio.e_s_id   = sub_entities.e_s_id
where   minprio.cat_prio = category.cat_prio

Linda

 
 
 

1. Join tables in a Group By Query

I know that if I join two tables in a query, I use an = operator on the joined
fields of my WHERE clause...

However, I am trying to do aggregate functions on the joined tables now and am
wondering whether the where clause is still the place to state my join
conditions... Should I move them to the HAVING clause? Duplicate them to the
having clause?

Right now I am thinking according to the documentation that I should duplicate
my where clause condition in the having clause..Afterall, I want to prevent
rows from displaying if they do not satisfy the join right?

Later,
  Gunther

2. Blob Sizes

3. Multi-table selection problem w/ SUM(), LEFT OUTER JOIN and GROUP BY

4. Seasonal Situation

5. join problem or maybe group :(

6. Code working with Type 1 Driver and Not with Type 4 Driver

7. Problem with SQL join/group-by in Unify database (long)

8. Microsoft Access and SQL

9. SQL, VB, Access, Queries, Join Problem, and Newbie Problem

10. Query using UDF joined to a sub query vs Temp Table to sub Query

11. Include Group and Non group columns in same query