Hi,
I have two tables, and I want to join them based on some priority rules:
If some special values exist in the joined subset,
join all of those values,
otherwise
join the first occurence of some other special value,
if that value also does not exist,
join the first occurence of a third value, and
if that value also does not exist,
join the first occurence of any value.
# create test tables
create table a (aid int,name char(10));
create table b (bid int,aid int,code char(1));
# insert dummy data
insert into a values (1,'rec 1'),(2,'rec 2'),(3,'rec 3'),(4,'rec 4');
insert into b values (1,1,'A'),(2,1,'B'),(3,1,'C'),(4,1,'D'),(5,1,'E');
insert into b values (6,2,'C'),(7,2,'C'),(8,2,'D'),(9,2,'E');
insert into b values (10,3,'D'),(11,3,'D'),(12,3,'E');
insert into b values (13,4,'E'),(14,4,'E'),(15,4,'F');
# listing all combinations
select a.aid,a.name,b.bid,b.code from a,b
where b.aid=a.aid order by a.aid,b.code;
+------+-------+------+------+
| aid | name | bid | code |
+------+-------+------+------+
| 1 | rec 1 | 1 | A |
| 1 | rec 1 | 2 | B |
| 1 | rec 1 | 3 | C |
| 1 | rec 1 | 4 | D |
| 1 | rec 1 | 5 | E |
| 2 | rec 2 | 6 | C |
| 2 | rec 2 | 7 | C |
| 2 | rec 2 | 8 | D |
| 2 | rec 2 | 9 | E |
| 3 | rec 3 | 10 | D |
| 3 | rec 3 | 11 | D |
| 3 | rec 3 | 12 | E |
| 4 | rec 4 | 13 | E |
| 4 | rec 4 | 14 | E |
| 4 | rec 4 | 15 | F |
+------+-------+------+------+
# priority rules:
# if codes A or B exist in b: show _all_ of _both_
# otherwise, show _first_ C if it exist
# otherwise, show _first_ D if it exist
# otherwise, show _first_ existing code
In the test data, each of these four rules apply to the corresponding record
in table a: record 1 match rule 1, record 2 match rule 2 and so on.
# What we want is this:
+------+-------+------+------+
| aid | name | bid | code |
+------+-------+------+------+
| 1 | rec 1 | 1 | A |
| 1 | rec 1 | 2 | B |
| 2 | rec 2 | 6 | C |
| 3 | rec 3 | 10 | D |
| 4 | rec 4 | 13 | E |
+------+-------+------+------+
What I've got so far, is this:
select distinct a.aid,a.name,b.bid,b.code
from a,b
left join b as pri1 on
pri1.aid=a.aid and
pri1.code in('A','B')
left join b as pri2 on
pri2.aid=a.aid and
pri2.code in('C')
left join b as pri2b on
pri2b.aid=a.aid and
pri2b.code in('C') and
pri2b.bid < b.bid
left join b as pri3 on
pri3.aid=a.aid and
pri3.code in('D')
left join b as pri3b on
pri3b.aid=a.aid and
pri3b.code in('D') and
pri3b.bid < b.bid
left join b as pri4 on
pri4.aid=a.aid and
pri4.code = b.code
left join b as pri4b on
pri4b.aid=a.aid and
pri4b.bid < b.bid
where b.aid=a.aid and
(
(not ISNULL(pri1.bid) and
b.code=pri1.code) or
(ISNULL(pri1.bid) and
not ISNULL(pri2.bid) and
ISNULL(pri2b.bid) and
b.code=pri2.code) or
(ISNULL(pri1.bid) and
ISNULL(pri2.bid) and
not ISNULL(pri3.bid) and
ISNULL(pri3b.bid) and
b.code=pri3.code) or
(ISNULL(pri1.bid) and
ISNULL(pri2.bid) and
ISNULL(pri3.bid) and
not ISNULL(pri4.bid) and
ISNULL(pri4b.bid))
)
order by a.aid,b.code;
This query works, but is there an easier way?
(version 3.23.39 on solaris)
TIA,
Roger Baklund,
Mobiliant AS
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
