Re: That killer 3rd join...

On Wed, Sep 06, 2000 at 10:52:34PM -0300, The Hermit Hacker wrote:
> SELECT
> stone_name, st.stone_uid, stone_modifies, stone_difficulty, stone_cost,
> silver.jtyp_name AS silver_name, (silver.metal_cost + stone_cost) AS Expr1,
> silver.jcombo_stats AS silver_stats,
> elec.jtyp_name AS electrum_name, (elec.metal_cost + stone_cost) AS Expr2,
> elec.jcombo_stats AS electrum_stats,
> gold.jtyp_name AS gold_name, (gold.metal_cost + stone_cost) AS Expr3,
> gold.jcombo_stats AS gold_stats,
> plat.jtyp_name AS plat_name, (plat.metal_cost + stone_cost) AS Expr4,
> plat.jcombo_stats AS plat_stats
> FROM
> stone_types st, silver, elec, gold, plat
> WHERE
> st.stone_uid = silver.stone_uid AND
> st.stone_uid = elec.stone_uid AND
> st.stone_uid = gold.stone_uid AND
> st.stone_uid = plat.stone_uid ;
>
> where 'silver','elec','gold','plat' are each:
>
> CREATE VIEW silver AS
> SELECT
> metal_name, jtyp_name, metal_cost, jc.stone_uid, mt.metal_uid, jc.jcombo_stats
> FROM
> jewellery_types jt, jewellery_combinations jc, metal_types mt
> WHERE
> jt.jtyp_uid = jc.jtyp_uid AND jc.metal_uid = mt.metal_uid AND mt.metal_uid = 1;
>
> I have to be missing something though, since I'm only getting back 23
> results, but they come back quick as anything ...

23 results is correct (or, infact, correct with the data I supplied).

Hmm - Interesting tho. It hadn't occured to me to drop the 'generic'
jcombo_query and replace that with specific views. Infact, what
I'd been doing was using

CREATE VIEW jcombo_query AS
SELECT
metal_name,
jtyp_name,
metal_cost,
jc.stone_uid,
mt.metal_uid,
jc.jcombo_stats
FROM
jewellery_types jt,
jewellery_combinations jc,
metal_types mt
WHERE
jt.jtyp_uid = jc.jtyp_uid AND
jc.metal_uid = mt.metal_uid ;

and then having the following:

CREATE VIEW silver AS
SELECT * FROM jcombo_query WHERE metal_uid = 1 ;

CREATE VIEW elec AS
...

However, when I did that, postgres went away. I'll give your idea a try,
it looks quite promising.

Oliver
--
If at first you don't succeed, skydiving is not for you...