Multiple Rows From One Row Where Data Is Missing 3

[boggg1]

In thread220-564239 I got the answer on how to put mutiple rows into one query row.


Returning to my original example...

Table A:
Product Stage Attribute Value
Alpha One Height V1
Alpha One Width V2
Alpha One Length V3
Alpha Two Height V4
Alpha Two Width V5
Alpha Two Length V6
Alpha Three Height V7
Alpha Three Width V8
Alpha Three Length V9

Table B:
Product Stage Quantity
Alpha One Q1
Alpha Two Q2
Alpha Three Q3

Carp gave me this solution...

SELECT b.stage, b.quantity, a1.value, a2.value, a3.value
FROM tableB b,
tableA a1,
tableA a2,
tableA a3
WHERE a1.product = b.product
AND a1.stage = b.stage
AND a2.product = b.product
AND a2.stage = b.stage
AND a3.product = b.product
AND a3.stage = b.stage
AND a1.attribute = 'HEIGHT'
AND a2.attribute = 'LENGTH'
AND a3.attribute = 'WIDTH'
ORDER BY b.stage;

This returns...

Stage Quantity Height Width Length
One Q1 V1 V2 V3
Two Q2 V4 V5 V6
Three Q3 V7 V8 V9

The table aliasing has allowed multiple rows to be returned in one row.


What if a row is missing from Table A ?

eg if the following row is missing...

Alpha Two Width V5

I still want to return...

Stage Quantity Height Width Length
One Q1 V1 V2 V3
Two Q2 V4 V6
Three Q3 V7 V8 V9

See how the missing data is just a blank (NULL or empty or whatever) but the row is still there.

I would like to continue to use Carp's structure as I am very familiar with it so how do I change Carp's query to achieve this ?

I have tried many possible solutions based on two principals...
1) Outer joins (syntax is familiar to me but I often struggle when table aliasing simultaneously with outer joins)
2) 'attribute = xxx OR attribute = NULL')
but with my massively more complex real queries I get too few rows or too many rows or nothing and I cannot see the logical pattern.

I had thought to write a similar simple database somewhere where I copuld experiment with basic solutions before scaling it up to my real queries but I have no write access to this particular corporate database and that would mean setting something up locally which may not be as logically equivalent as I need. After all there is something I do not understand here. This offers no guarantee of an answer and may not help me understand fundamentally the principal. I thought I'd try you excellent people next.

Boggg1

 

[dnoeth]

If you want to stick with joins, you can use LEFT OUTER JOINs to table a:
FROM tableB b LEFT JOIN
tableA a1 LEFT JOIN
tableA a2 LEFT JOIN
tableA a3

This is probably better than using proprietary syntax (Oracle?):
WHERE a1.product (+) = b.product
AND a1.stage (+) = b.stage
AND a2.product (+) = b.product
AND a2.stage (+) = b.stage
AND a3.product (+) = b.product
AND a3.stage (+) = b.stage
AND a1.attribute (+) = 'HEIGHT'
AND a2.attribute (+) = 'LENGTH'
AND a3.attribute (+) = 'WIDTH'


But you better try BillDHS' solution using MAX/CASE:
Select
A.product, A.stage, B.Quantity,
MAX(CASE WHEN Attribute = 'height' THEN value END),
MAX(CASE WHEN Attribute = 'width' THEN value END),
MAX(CASE WHEN Attribute = 'length' THEN value END)
From Table A Left Outer Join Table B
On A.Product = B.Product
And A.stage = B.Stage
Group by
A.product, A.stage, B.Quantity

Dieter

 

[boggg1]

Thanks. My syntax is the proprietary one you mention and it is Oracle as you guessed. Unfortunatley the middle solution does not work in my case but it is my problem to get from the theoretical example to a working query. Your answer is also useful because I have never seen a column outer joined to a string before. Column to column joins are in every tutorial but never column outer joined to string. I had guessed this was correct syntax in my own work but it is good to have this confirmed. Have a star.

Maybe I should learn and experiment with the other SQL forms. Is it possible to define what you mean by "better" solutions or is this just a wise feeling from an old hand ?

Thanks again

Boggg1

 

[dnoeth]

Newer versions of Oracle already support CASE (8i, but you can also use MAX(DECODE(Attribute, 'height',value END)) instead) and OUTER JOIN (9i)

"Better" means:
- MAX/GROUP BY may probably run faster than three joins (depending on DBMS and indexes)
- OUTER JOIN is easier to write than (+) and you can port it to almost any other DBMS

Dieter

 

[boggg1]

Thanks. I will go away and digest and play while you savour your stars; have another.

Boggg1

 

[JPD3]

What did you do to resolve this problem? I am currently battling the same monster.

 

[boggg1]

JPD3, my version of Oracle does not support all of the options here and in the end I used (+) (outer join). This is fast once I got the query in the right order (it matters a lot despite Oracle optimisation) and it is highly unlikely that I would ever port the database to anywhere so (+) is OK. My real queries are MUCH more complex than the above, I just wanted the principal explained.

So the (+) solution works and the MAX(CASE WHEN ... THEN ... END) and MAX(DECODE(... END)) are repeated on many sites across the internet so they must work too.

Using these techniquesI constantly find myself running out of outer joins (a table is only allowed to be outer joined to one other and I seem to need mutiple outer joins) so I end up with compromises in my queries, not always making all the joins I would like. One fix would be to organise the data differently (redesign the database) to make the data simpler to query but it looks like I'm going to run out of job before I get someone to do it. I should ask about this problem from the real experts on this site, I guess.

I think dnoeth deserves a star from you if you use his solution - he certainly helped me.

By the way do you understand what an outer join and table aliasing are (because this will all be very hard to understand until you've grabbed the simple principals involved) ?

Good luck.
Boggg1

 

[JPD3]

Yep, I just got it working. My new love is left outer joins.