Join

Afternoon all...

Can someone help me with a join...as I now have no hair and am having
trouble typing because of all the caffine...

I have three tables: TABLE1, TABLE2 and TABLE3.

All tables have the IDENTIFIER, QUANTITY and DATE.

What I would like is to be able to produce a query that gives a list
(for CURDATE()) a list of identifiers and quantities where each
identifier is shown once but if they appear in multiple tables to sum
the resulting quantities together...

thank you very much

richard

On 23 Nov, 16:39, rjre <rjredwa...@googlemail.com> wrote:
> Afternoon all...
>
> Can someone help me with a join...as I now have no hair and am having
> trouble typing because of all the caffine...
>
> I have three tables: TABLE1, TABLE2 and TABLE3.
>
> All tables have the IDENTIFIER, QUANTITY and DATE.
>
> What I would like is to be able to produce a query that gives a list
> (for CURDATE()) a list of identifiers and quantities where each
> identifier is shown once but if they appear in multiple tables to sum
> the resulting quantities together...
>
> thank you very much
>
> richard

I'm not sure I understand your question, but it sounds like what you
need is a union, not a join (even though it might be possible to
express the same thing via joins, it is not very intuitive). Is this
what you mean?

select identifier, some_date, sum(quantity) from (
select identifier, some_date, quantity from t1
union all
select identifier, some_date, quantity from t2
union all
select identifier, some_date, quantity from t3
) x group by identifier, some_date;


/Lennart

rjre wrote:
> Afternoon all...
>
> Can someone help me with a join...as I now have no hair and am having
> trouble typing because of all the caffine...
>
> I have three tables: TABLE1, TABLE2 and TABLE3.
>
> All tables have the IDENTIFIER, QUANTITY and DATE.
>
> What I would like is to be able to produce a query that gives a list
> (for CURDATE()) a list of identifiers and quantities where each
> identifier is shown once but if they appear in multiple tables to sum
> the resulting quantities together...
>
> thank you very much
>
> richard

Richard,

Your question is confusing. How about the layout of your tables, some
sample data, and the results you hope to achieve?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Nov 23, 10:39*am, rjre <rjredwa...@googlemail.com> wrote:
> Afternoon all...
>
> Can someone help me with a join...as I now have no hair and am having
> trouble typing because of all the caffine...
>
> I have three tables: TABLE1, TABLE2 and TABLE3.
>
> All tables have the IDENTIFIER, QUANTITY and DATE.

If these are the same entity why are they in 3 tables?

>
> What I would like is to be able to produce a query that gives a list
> (for CURDATE()) a list of identifiers and quantities where each
> identifier is shown once but if they appear in multiple tables to sum
> the resulting quantities together...
>
> thank you very much
>
> richard

On 23 Nov, 16:25, Lennart <erik.lennart.jons...@gmail.com> wrote:
> On 23 Nov, 16:39, rjre <rjredwa...@googlemail.com> wrote:
>
>
>
>
>
> > Afternoon all...
>
> > Can someone help me with a join...as I now have no hair and am having
> > trouble typing because of all the caffine...
>
> > I have three tables: TABLE1, TABLE2 and TABLE3.
>
> > All tables have the IDENTIFIER, QUANTITY and DATE.
>
> > What I would like is to be able to produce a query that gives a list
> > (for CURDATE()) a list of identifiers and quantities where each
> > identifier is shown once but if they appear in multiple tables to sum
> > the resulting quantities together...
>
> > thank you very much
>
> > richard
>
> I'm not sure I understand your question, but it sounds like what you
> need is a union, not a join (even though it might be possible to
> express the same thing via joins, it is not very intuitive). Is this
> what you mean?
>
> select identifier, some_date, sum(quantity) from (
> * * select identifier, some_date, quantity from t1
> * * union all
> * * select identifier, some_date, quantity from t2
> * * union all
> * * select identifier, some_date, quantity from t3
> ) x group by identifier, some_date;
>
> /Lennart- Hide quoted text -
>
> - Show quoted text -

perfect! - thx vm