CREATE TABLE taba AS
SELECT '1' a_id, 5 ccost FROM DUAL
UNION ALL
SELECT '2' a_id, 5 ccost FROM DUAL;
CREATE TABLE tabb AS
SELECT '1' b_id,
5 ccost1,
5 ccost2,
'2' a_id
FROM DUAL
UNION ALL
SELECT '2' b_id,
5 ccost1,
5 ccost2,
'2' a_id
FROM DUAL;
SELECT a.a_id, CASE WHEN b.sum_cost IS NULL THEN 'taba' ELSE 'tabb' END
from_table,
CASE WHEN b.sum_cost IS NULL THEN ccost ELSE b.sum_cost END
the_cost
FROM taba a
LEFT OUTER JOIN
( SELECT SUM( x.ccost1 + x.ccost2 ) sum_cost, x.a_id
FROM tabb x
GROUP BY x.a_id) b
ON a.a_id = b.a_id
ORDER BY a.a_id;
A_ID FROM_TABLE THE_COST
---- ---------- ----------
1 taba 5
2 tabb 20
2 rows selected.
Mike
On Tue, Aug 24, 2010 at 12:17 PM, Mark <markdanielmal...@gmail.com> wrote:
> Sorry, there is only 2 tables. By referenced, I mean the following:
>
> Table A
> a_ID cost
> 1 5
> 2 5
>
> Table B
> b_ID cost_1 cost_2 a_ID
> 1 5 5 2
> 2 5 5 2
>
> Expected Results:
> For record 1 in Table A the Cost would be 5 and for record 2 the Cost
> would be 20.
>
> On Aug 23, 7:47 pm, ddf <orat...@msn.com> wrote:
> > On Aug 23, 7:29 pm, Mark <markdanielmal...@gmail.com> wrote:
> >
> >
> >
> > > I need to translate the pseudo following into a SQL statement
> >
> > > 3 tables linked in a one to many relationship
> >
> > > Table A
> > > a_ID
> > > cost
> >
> > > Table B
> > > b_ID
> > > cost_1
> > > cost_2
> > > *a_ID
> >
> > > if Table A is referenced in Table B then
> > > return the sum (cost_1 + cost_2) from Table B of all the
> > > referenced records
> > > otherwise
> > > return cost from Table A
> >
> > What is the third table? I see only two.
> >
> > Have you considered using a CASE statement for this? Define what you
> > mean by 'if Table A is referenced in Table B' because no one can help
> > you without that information.
> >
> > David Fitzjarrell
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
