I'll start:


I can do this with tons of nested queries, but there has got to be a better way.
I have a table of related products like

ONE | TWO | THREE | FOUR
A1       A2       A3       A4
B1       B2       B3       B4
C1       C2       C3       C4
...

I have another table with product details

Product | detail 1 | detail 2| ...
A1           5453           245
B3           453           2342
A2           675           76
A1           458           357
...

I want to select somethign that relates the details of the products, using the relation of the first table, e.g

One | SUM(detail 1)| AVG(detail 2) | Two | SUM(detail 1) | AVG(detail 2) | ...
A1       9823           7455           A2           564846           682342
B1 .....


The problem is table two has multiple entries per product, and different times per product, so A2 might be listed 30 times and A1 only twice.
Thoughts?

[Edited on May 15, 2009 at 2:38 PM. Reason : .]

[Edited on May 15, 2009 at 2:39 PM. Reason : spacing]

5/15/2009 2:36:28 PM

message_topic.aspx?topic=484972

5/15/2009 2:47:09 PM

doh, lock, delete, suspend

5/15/2009 2:53:36 PM

SELECT sick_burn FROM qntmfred WHERE thread = '[old]';

5/15/2009 3:11:29 PM

^ hahaha

5/15/2009 3:15:31 PM

I'm going to name your tables Alpha and Beta to differentiate. I'm also going to assume your first table (alpha) will only ever have 4 fields; that it will always have four products per row. This really should be a star schema BTW.

ALPHA
ONE | TWO | THREE | FOUR
A1 A2 A3 A4
B1 B2 B3 B4
C1 C2 C3 C4

BETA
Product | detail 1 | detail 2| ...
A1 5453 245
B3 453 2342
A2 675 76
A1 458 357

SELECT
    alpha.one,
    sum(beta1.detail1),
    avg(beta1.detail2),
    alpha.two,
    sum(beta2.detail1),
    avg(beta2.detail2),
    alpha.three,
    sum(beta3.detail1),
    avg(beta3.detail2),
    alpha.four,
    sum(beta4.detail1),
    avg(beta4.detail2)
  FROM alpha
    LEFT JOIN beta beta1 ON
      (alpha.one = beta1.product)
    LEFT JOIN beta beta2 ON
      (alpha.two = beta2.product)
    LEFT JOIN beta beta3 ON
      (alpha.three = beta3.product)
    LEFT JOIN beta beta4 ON
      (alpha.four = beta4.product)

5/16/2009 1:29:29 PM

whoops other thread >.<

[Edited on July 31, 2009 at 8:27 AM. Reason : wrong thread]

7/31/2009 8:26:55 AM

Nice coding Phil.

7/31/2009 10:16:24 AM