Moderately complex query (how-to?)

[Sam]

For this example I have eliminated (most of) the unreferenced
columns from the table definitions.

My problem is: how does one formulate a select statement or
create a view in SQL (I'm using MySQL (Ver 14.7 Distrib 4.1.16))
that implements the pseudo-code that follows the table
definitions? BTW, I _have_ read the manual (several times).

create table t2 ( # about 1K rows
n2 smallint unsigned not null auto_increment unique key,
n1 smallint unsigned not null references t1 (n1),
s2 varchar(90) primary key);

create table t3 ( # about 50K rows
n3 mediumint unsigned not null auto_increment unique key,
s3 varchar(99) primary key);

create table t4 ( # about 1.6M rows
k4 char(15) primary key,
dn mediumint unsigned references t3 (n3),
vn smallint unsigned references t2 (n2),
s4 varchar(120) not null);

create table t5 ( # about 30K rows
k5 char(15) not null key references t4 (ik),
vn smallint unsigned not null references t2 (n2));

If I were doing this in a procedural language, I would:

loop for each row of t5
using row from t4 with t4.k4==t5.k5
// row always exists and t4.vn != t5.vn when t4.k4==t5.k5
if dn NULL go to top of loop end_if // 60%-80% are NULL
display t1.s1 as ac where t1.n1==t5.vn
display t1.s1 as ai where t1.n1==t4.vn
display t4.s4 as fn
display t3.s3 as di where t3.n3==t4.dn
end_using
end_loop

TIA

[Gordon Burditt]

>For this example I have eliminated (most of) the unreferenced

columns from the table definitions.

My problem is: how does one formulate a select statement or
create a view in SQL (I'm using MySQL (Ver 14.7 Distrib 4.1.16))
I don't think you get views in MySQL until 5.0.
that implements the pseudo-code that follows the table
definitions? BTW, I _have_ read the manual (several times).

create table t2 ( # about 1K rows
n2 smallint unsigned not null auto_increment unique key,
n1 smallint unsigned not null references t1 (n1),
s2 varchar(90) primary key);

create table t3 ( # about 50K rows
n3 mediumint unsigned not null auto_increment unique key,
s3 varchar(99) primary key);

create table t4 ( # about 1.6M rows
k4 char(15) primary key,
dn mediumint unsigned references t3 (n3),
vn smallint unsigned references t2 (n2),
s4 varchar(120) not null);

create table t5 ( # about 30K rows
k5 char(15) not null key references t4 (ik),
vn smallint unsigned not null references t2 (n2));

If I were doing this in a procedural language, I would:

loop for each row of t5
using row from t4 with t4.k4==t5.k5
// row always exists and t4.vn != t5.vn when t4.k4==t5.k5
if dn NULL go to top of loop end_if // 60%-80% are NULL
display t1.s1 as ac where t1.n1==t5.vn
display t1.s1 as ai where t1.n1==t4.vn
display t4.s4 as fn
display t3.s3 as di where t3.n3==t4.dn
end_using
end_loop

SELECT
t1a.s1 as ac,
t1b.s1 as ai,
t4.s4 as fn,
t3.s3 as di
FROM t5
LEFT JOIN t4 ON t4.k4 = t5.k5
LEFT JOIN t1 as t1a ON t1a.n1 = t5.vn
LEFT JOIN t1 as t1b ON t1b.n1 = t4.vn
LEFT JOIN t3 ON t3.n3 = t4.dn
WHERE t4.dn is not null and t4.vn != t5.vn;

I didn't check all the foreign key references to see if there would
be a difference between a left join and a regular join (whether
matching records are guaranteed to exist or not), and in any case,
you didn't provide a table schema for table t1. I suspect you
really meant for references to t1 to be to t2.

You can join against the same table twice by using aliases to give
each copy different names, then using the aliases to reference
fields.

Gordon L. Burditt