CODEWITHSUNDEEP
sqloracle-databaseself-join
Logarr
Logarr

Reputation: 2417

Multiple self joins plus one inner join

I have two tables: ck_startup and ck_price. The price table contains the columns cu_type, prd_type, part_cd, qty, and dllrs. The startup table is linked to the price table through a one-to-many relationship on ck_startup.prd_type_cd = ck_price.prd_type.

The price table contains multiple entries for the same product/part/qty but under different customer types. Not all customer types have the same unique combination of those three values. I'm trying to create a query that will do two things:

  1. Join some columns from ck_startup onto ck_price (description, and some additional values).
  2. Join ck_price onto itself with a dllrs column for each customer type. So in total I would only have one instance of each unique key of product/part/qty, and a value in each customer's price column if they have one.

I've never worked with self joining tables, and so far I can only get records to show up where both customers have the same options available.

And because someone is going to demand I post sample code, here's the crappy query that doesn't show missing prices:

select pa.*, pac.dllrs from ck_price pa
join ck_price pac on pa.prd_type = pac.prd_type and pa.part_carbon_cd = pac.part_carbon_cd and pa.qty = pac.qty
where pa.cu_type = 'A' and pac.cu_type = 'AC';

EDIT: Here's sample data from the two tables, and how I want them to look when I'm done:

CK_STARTUP
+-----+-----------------+-------------+
| CD  |       DSC       | PRD_TYPE_CD |
+-----+-----------------+-------------+
| 3D  | Stuff           | SKD3        |
| DC  | Different stuff | SKD         |
| DN2 | Similar stuff   | SKD         |
+-----+-----------------+-------------+

CK_PRICE
+---------+-------------+---------+-----+-------+
| CU_TYPE | PRD_TYPE_CD | PART_CD | QTY | DLLRS |
+---------+-------------+---------+-----+-------+
| A       | SKD3        |       1 | 100 |    10 |
| A       | SKD3        |       1 | 200 |    20 |
| A       | SKD3        |       1 | 300 |    30 |
| A       | SKD         |       1 | 100 |    50 |
| A       | SKD         |       1 | 200 |   100 |
| AC      | SKD3        |       1 | 300 |    30 |
| AC      | SKD         |       1 | 100 |   100 |
| AC      | SKD         |       1 | 200 |   200 |
| AC      | SKD         |       1 | 300 |   300 |
| AC      | SKD         |       1 | 400 |   400 |
+---------+-------------+---------+-----+-------+

COMBO:
+----+-----------------+---------+-----+---------+----------+
| CD |       DSC       | PART_CD | QTY | DLLRS_A | DLLRS_AC |
+----+-----------------+---------+-----+---------+----------+
| 3D | Stuff           |       1 | 100 | 10      | null     |
| 3D | Stuff           |       1 | 200 | 20      | null     |
| 3D | Stuff           |       1 | 300 | 30      | 60       |
| DC | Different stuff |       1 | 100 | 50      | 100      |
| DC | Different stuff |       1 | 200 | 100     | 200      |
| DC | Different stuff |       1 | 300 | null    | 300      |
| DC | Different stuff |       1 | 400 | null    | 400      |
+----+-----------------+---------+-----+---------+----------+

Upvotes: 0

Views: 2664

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Ok, take a look at below query and at the results:

SELECT *
FROM   (SELECT
          cs.cd, cs.dsc, cp.part_cd, cp.qty, cp.dllrs, cp.cu_type
        FROM ck_startup cs
          JOIN ck_price cp ON (cs.prd_type_cd = cp.prd_type_cd))
PIVOT (SUM(dllrs) AS dlllrs FOR (cu_type) IN ('A' AS a, 'AC' AS ac))
ORDER BY cd, qty
;

Output:

CD       DSC                  PART_CD     QTY   A_DLLLRS  AC_DLLLRS
-------- ----------------- ---------- ------- ---------- ----------
3D       Stuff                      1     100         10            
3D       Stuff                      1     200         20            
3D       Stuff                      1     300         30         30 
DC       Different stuff            1     100         50         50 
DC       Different stuff            1     200        100        100 
DC       Different stuff            1     300                   150 
DC       Different stuff            1     400                   200 
DN2      Similar stuff              1     100         50         50 
DN2      Similar stuff              1     200        100        100 
DN2      Similar stuff              1     300                   150 
DN2      Similar stuff              1     400                   200

It is not what you would expect, because I do not understand why you have different values in DLLRS_AC column that are in the CK_PRICE table? I mean, for example, why do you have 400 in last line of your output, not 200? Why is this value doubled (as others are in DLLRS_AC column)?

If you are using Oracle 10g, you can achieve the same result using DECODE and GROUP BY, take a look:

SELECT
        cd,
        dsc,
        part_cd,
        qty,
        SUM(DECODE(cu_type, 'A', dllrs, NULL)) AS dllrs_a,
        SUM(DECODE(cu_type, 'AC', dllrs, NULL)) AS dllrs_ac
FROM (
  SELECT
    cs.cd, cs.dsc, cp.part_cd, cp.qty, cp.dllrs, cp.cu_type
  FROM ck_startup cs
    JOIN ck_price cp ON (cs.prd_type_cd = cp.prd_type_cd)
  )
GROUP BY cd, dsc, part_cd, qty
ORDER BY cd, qty;

Result is the same.

If you want to read more about pivoting, I recommend article by Tim Hall: Pivot and Unpivot at Oracle Base

Upvotes: 1

Related Questions