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...