SQL join on many queries

Question

Hi,

I have a table with a structure such as table(PK, a, b, c, d, e, f, g).

And I have many queries that I want to join:

select PK, sum(c) where...

JOIN

select PK, sum(e) where...

JOIN

select PK, sum(g) where ...

JOIN

 select PK,a,b,d,f

Every sum(c|e|g) is actually a

select sum(c|e|g) from .... where...

because there are many conditions involved (not all c|e|g's must be added)

Is this the best way to do it? I was suggested to write it in PL / SQL, which I would have to learn. If its the way to go, i'll do it, but im not sure whats wrong with the solution shown above.

Edit

Im pretty sure its a Join. Here's what I want.

I need to get a result set in the form:

PK, a,b,COMPLEX_SUM_ON_C,d,COMPLEX_SUM_ON_D,f,COMPLEX_DUM_ON_G

so I thought of joining many queries to get this result.

Each of the COMPLEX... is another select (select sum...). This is a very big table, and writing

select a,b,(select sum..),d,(select sum...),f,(select sum...)

will yield bad performance (so I was told to remove it)

I've edited my query above.

Thanks in advance.

Answer 1

I think you mean "UNION" not "JOIN". Whether is the best way depends on what you're trying to achieve.

Answer 2

An example of a JOIN is the following:

Select a.col1, b.col2
FROM table1 a, table2 b
WHERE a.key = b.key;

which can also be written:

SELECT a.col1, b.col2
FROM table1 a
INNER JOIN table2 b
ON a.key = b.key;

Edit:

After reading your re-edit of the original question, you can probably use a JOIN. JOINs can be used when you have related data in more than one table, or you can specifiy the same table multiple times. I have used both kinds with Oracle. Here's an example of the latter kind which will hopefully help you:

SELECT t1.a, t1.b, t3.sum(c), t2.d, t4.sum(e), t1.f, t5.sum(g)
FROM table1 t1, table1 t2, table1 t3, table1 t4, table1 t5
WHERE  t1.a = 'hello'
AND t2.a = 'world'
AND t3.c = 10
AND t4.e = 20
AND t5.g = 100
GROUP BY t1.a, t1.b, t2.d, t1.f;

Answer 3

This is not a well-defined problem (yet).

Assuming PK is your primary key (i.e. unique, by definition), then

SELECT PK, SUM(c)
FROM tbl
GROUP BY PK

is ALWAYS the same as

SELECT PK, c
FROM tbl

So grouping (and aggregating) is relatively meaningless.

In your expected results:

PK, a,b,COMPLEX_SUM_ON_C,d,COMPLEX_SUM_ON_D,f,COMPLEX_DUM_ON_G

How are COMPLEX_SUM_ON_C, COMPLEX_SUM_ON_D, COMPLEX_DUM_ON_G related to PK?

We know how a, b, d, f are related to PK, because for each PK, one can identify the one and only a, b, d, f on the same row.

Answer 4

Perhaps joining to derived tables would work? Hard to say as you haven;t given us the details we need. Give us your query as it stands right now that is too slow and we can help. Trying to simplify it is not helping us see the problem.