Databases
StackOverflow2013
Postsdbo
POMultiple self joins plus one inner join
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple self joins plus one inner join
    text
    copied!<p>I have two tables: <code>ck_startup</code> and <code>ck_price</code>. The price table contains the columns <code>cu_type</code>, <code>prd_type</code>, <code>part_cd</code>, <code>qty</code>, and <code>dllrs</code>. The startup table is linked to the price table through a one-to-many relationship on <code>ck_startup.prd_type_cd = ck_price.prd_type</code>.</p> <p>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:</p> <ol> <li>Join some columns from <code>ck_startup</code> onto <code>ck_price</code> (description, and some additional values).</li> <li>Join <code>ck_price</code> onto itself with a <code>dllrs</code> 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.</li> </ol> <p>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.</p> <p>And because someone is going to demand I post sample code, here's the crappy query that doesn't show missing prices:</p> <pre><code>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'; </code></pre> <p><strong>EDIT:</strong> Here's sample data from the two tables, and how I want them to look when I'm done:</p> <pre> 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 | +----+-----------------+---------+-----+---------+----------+ </pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload