outputting tables with similar name

#11

On 8/23/2012 5:13 AM, The Natural Philosopher wrote:

Quote:

Scott Johnson wrote:
On 8/22/2012 4:53 PM, The Natural Philosopher wrote:
Gordon Burditt wrote:
Yes I like the idea of forcing the value using the alias.

This is the first time using ALIAS and so far I am impressed with
the results and can see many uses for it in the future.

Scotty
The one unavoidable necessity for it is in case like this where you
have a join including two identical field names from two different
tables.

Another place where using an alias on table names, and perhaps field
names as well, is where you are joining a table to itself (the two
instances of the same table necessarily have all field names
identical). You might, for example, be doing a 3-way join between
child, mother, and father in the person table, and be interested
in the blood types of each.

SELECT c.id, c.blood_type as child_blood_type,
f.blood_type as father_blood_type, m.blood_type as
mother_blood_type
FROM person as c
LEFT JOIN person as f ON c.father_id = f.id
LEFT JOIN person as m ON c.mother_id = m.id;

+1

I remember having to do exactly that.

Multi-level kitting in a BOM type system

If you want a breakdown of all parts to say 5 levels deep, where
component A is part of sub assembly B which is part of less-sub-assembly
D which is part of Really Big sub-assembly E which is part of finished
product F...and all you have is a table of parts and subassemblies and
another table saying parent F contains child E and parent E contains
Child D...and so on.. and you want to know how many finished products
contain the umbelliferous sprag washer, one way or another, that is on 6
weeks delivery..and who has them on back order...so you can call them
and tell them the bad news....

Sheesh, I would end up staring at my screen for a week just trying to
figure out that algorithm.

well it ws a bit like that.

The end result was two pages of PHP. 90% of the time was getting two SQL
statements to be correct.

Here's a typical one

SELECT distinct (NOT isnull(t0.id)) as is_kit,
CONCAT_WS(':',t1.name,t2.name,t3.name,t4.name) as catname,
product.name, kits.qty, overhead, product.id
FROM kits, product
LEFT join category as t4 on t4.id = product.category
LEFT JOIN category AS t3 ON t3.id = t4.parent
LEFT JOIN category AS t2 ON t2.id = t3.parent
LEFT join category as t1 on t1.id=t2.parent
LEFT JOIN kits as t0 on t0.parent = product.id
WHERE kits.parent='%d' and product.id=kits.child
ORDER by overhead DESC, is_kit, catname, name

and no, I am out of the zone now and can't explain what it does either
any more:-)

Suffice to say that for just long enough to get it right, I did actually
understand what it all meant...

haha.

I have so many times looked back at code and thought, I must of been a
genius back then because I can't understand my own logic now.

#12

Scott Johnson wrote:

Quote:

On 8/23/2012 5:13 AM, The Natural Philosopher wrote:
Scott Johnson wrote:
On 8/22/2012 4:53 PM, The Natural Philosopher wrote:
Gordon Burditt wrote:
Yes I like the idea of forcing the value using the alias.

This is the first time using ALIAS and so far I am impressed with
the results and can see many uses for it in the future.

Scotty
The one unavoidable necessity for it is in case like this where you
have a join including two identical field names from two different
tables.

Another place where using an alias on table names, and perhaps field
names as well, is where you are joining a table to itself (the two
instances of the same table necessarily have all field names
identical). You might, for example, be doing a 3-way join between
child, mother, and father in the person table, and be interested
in the blood types of each.

SELECT c.id, c.blood_type as child_blood_type,
f.blood_type as father_blood_type, m.blood_type as
mother_blood_type
FROM person as c
LEFT JOIN person as f ON c.father_id = f.id
LEFT JOIN person as m ON c.mother_id = m.id;

+1

I remember having to do exactly that.

Multi-level kitting in a BOM type system

If you want a breakdown of all parts to say 5 levels deep, where
component A is part of sub assembly B which is part of
less-sub-assembly
D which is part of Really Big sub-assembly E which is part of finished
product F...and all you have is a table of parts and subassemblies and
another table saying parent F contains child E and parent E contains
Child D...and so on.. and you want to know how many finished products
contain the umbelliferous sprag washer, one way or another, that is
on 6
weeks delivery..and who has them on back order...so you can call them
and tell them the bad news....

Sheesh, I would end up staring at my screen for a week just trying to
figure out that algorithm.

well it ws a bit like that.

The end result was two pages of PHP. 90% of the time was getting two SQL
statements to be correct.

Here's a typical one

SELECT distinct (NOT isnull(t0.id)) as is_kit,
CONCAT_WS(':',t1.name,t2.name,t3.name,t4.name) as catname,
product.name, kits.qty, overhead, product.id
FROM kits, product
LEFT join category as t4 on t4.id = product.category
LEFT JOIN category AS t3 ON t3.id = t4.parent
LEFT JOIN category AS t2 ON t2.id = t3.parent
LEFT join category as t1 on t1.id=t2.parent
LEFT JOIN kits as t0 on t0.parent = product.id
WHERE kits.parent='%d' and product.id=kits.child
ORDER by overhead DESC, is_kit, catname, name

and no, I am out of the zone now and can't explain what it does either
any more:-)

Suffice to say that for just long enough to get it right, I did actually
understand what it all meant...

haha.

I have so many times looked back at code and thought, I must of been a
genius back then because I can't understand my own logic now.

It gets worse. Ive often looked at usenet posts and code I have written
and stuff I find on the internet and thought 'at last, someone who
really understands'...and then noticed it was me that wrote it.

What is more disturbing was discovering an old fan fold of wads of C and
assembler and thinking, "I know I wrote this, and I got paid to write
it, but I cant remember actually writing any of it at all!" As if one
channeled it from another dimension, and the memory of it is erased when
the zone is left..

--
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

outputting tables with similar name

comp.databases.mysql comp.databases.mysql

Re: outputting tables with similar name - 08-23-2012 , 01:08 PM

Re: outputting tables with similar name - 08-23-2012 , 03:57 PM