Re: Getting two fields from the one table.

From: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Wed, 26 Nov 2003 07:51:27 -0500
Message-ID: <AvednZSabc91PFmiRVn-ig@comcast.com>

"Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message news:N5Wwb.73$wh7.3507_at_nnrp1.ozemail.com.au...

| Thanks McStock & VC.
|
| I can't get it to work. What should the resulting SQL look like?
|
| -- 
|
| Julian Milano
|
|

here's the basic concept:

instead of this:

SQL> select
  2 prod.name

  3  , prod.id
  4  , prc.price_class
  5  , prc.price

  6 from
  7 s_product prod
  8 , s_price prc
  9 where
 10 prc.product_id = prod.id
 11 order by
 12 prod.name
 13 , prc.price_class
 14 /
...

NAME                                                       ID PRICE_CLASS

PRICE

-------------------------------------------------- ---------- ----------- --
--------
Ace Ski Boot                                            10012           1
175
Ace Ski Boot                                            10012           2
178.5
Ace Ski Pole                                            10022           1
21.95
Ace Ski Pole                                            10022           2
23.05
Alexeyer Pro Lifting Bar                                40421           1
85
Alexeyer Pro Lifting Bar                                40421           2
77.35
Alomar Glove                                            50418           1
75
Alomar Glove                                            50418           2

95.25
...

do this:

  1 select

  2    prod.name
  3  , prod.id
  4  , prc1.price as CLASS1
  5  , prc2.price as CLASS2

  6 from

  7    s_product prod
  8  , s_price prc1
  9  , s_price prc2

 10 where
 11 prc1.price_class = 1
 12 and
 13 prc1.product_id = prod.id
 14 and
 15 prc2.price_class = 2
 16 and
 17 prc2.product_id = prod.id
 18 order by
 19* prod.name
SQL> /
...

NAME                                                       ID     CLASS1

CLASS2

-------------------------------------------------- ---------- ---------- ---
-------
Ace Ski Boot                                            10012        175
178.5
Ace Ski Pole                                            10022      21.95
23.05
Alexeyer Pro Lifting Bar                                40421         85
77.35
Alomar Glove                                            50418         75

95.25
...

here's the DECODE version someone suggested:

SQL> select
  2 prod.name
  3 , prod.id
  4 , max(
  5 decode(

  6      prc.price_class
  7     ,1,prc.price
  8     ,null
  9     )

 10 ) as CLASS1
 11 , max(
 12 decode(

 13      prc.price_class
 14     ,2,prc.price
 15     ,null
 16     )

 17 ) as CLASS2
 18 from
 19 s_product prod
 20 , s_price prc
 21 where
 22 prc.product_id = prod.id
 23 group by
 24 prod.name
 25 , prod.id
 26 /
...

NAME                                                       ID     CLASS1

CLASS2

-------------------------------------------------- ---------- ---------- ---
-------
Ace Ski Boot                                            10012        175
178.5
Ace Ski Pole                                            10022      21.95
23.05
Alexeyer Pro Lifting Bar                                40421         85

77.35
...

with the decode, you artificially must select the max of each prices, and you must include all other columns in the GROUP BY -- which can then get quite large

again, the reference suggested earlier is http://asktom.oracle.com, search for PIVOT (which is a concept, not a SQL keyword or function)

• Mark Stock

Received on Wed Nov 26 2003 - 06:51:27 CST