Moderately complex query (how-to?)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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 != 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
    display t1.s1 as ai where
    display t4.s4 as fn
    display t3.s3 as di where t3.n3==t4.dn


Re: Moderately complex query (how-to?)

Quoted text here. Click to load it

I don't think you get views in MySQL until 5.0.

Quoted text here. Click to load it

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

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

                        Gordon L. Burditt

Site Timeline