James M. Gonzalez wrote:
> -----Original Message-----
> From: Danny Stolle [mailto:d.stolle@stripped]
> Sent: 15 June 2005 21:09
> To: James M. Gonzalez
> Subject: Re: help joining tables in a query
>
> James M. Gonzalez wrote:
>
>>Greetings,
>>
>>
>>
>>I'm facing a difficult query at the moment. I have tried many
>
> different
>
>>queries but still not get the desired result.
>>
>>
>>
>>My case:
>>
>>
>>
>>Tables:
>>
>>
>>
>>shipped ( id, shipdate, sn);
>>
>>undelivered (id, undate, sn);
>>
>>return (id, redate, sn);
>>
>>
>>
>>
>>
>>
>>
>>I need the following output:
>>
>>
>>
>>DATE SHIPMENTS UNDELIVERED RETURNS
>>
>>Fri 1 may 2005 87 11 4
>>
>>Sat 2 may 2005 82 17 5
>>
>>... .. ...
>
> ...
>
>>
>>
>>
>>
>>
>>
>>So far, Im have succesfulyl manage to get the result using just one
>>table:
>>
>>
>>
>>DATE SHIPMENTS
>>
>>Fri 1 may 2005 87
>>
>>Sat 2 may 2005 82
>>
>>... ..
>>
>>
>>
>>
>>
>>
>>
>>With the query:
>>
>>
>>
>>SELECT DATE_FORMAT(shipdate,'%a %e %b %Y'),
>>
>>COUNT(shipdate)
>>
>>FROM shipped
>>
>>WHERE (YEAR(shipdate) = 2004) AND (MONTH(shipdate) = 05)
>>
>>GROUP BY shipdate
>>
>> ORDER BY shipdate DESC
>>
>>
>>
>>
>>
>>However, joining the 3 tables and getting the right results is being a
>>nightmare. I have already tried different left and right joins, with
>
> no
>
>>success. Please any help, hints, or light on the query will be
>
> greately
>
>>appreciated.
>>
>>
>>
>>James.
>>
>>
>>
>>
>>
>>
>
>
> James,
>
> please check your table design. i can't find any relational keys, like a
>
> primarry key and a foreign key (or would that be the 'sn' as in 'serial
> number'?). if the tables can't relate to each other it is not possible
> to get a reliable resultset.
>
> Danny Stolle
> Netherlands
>
>
> ------------------------------------------------------
>
> Thanks a lot for your reply!! Actually yes, sn means "serial numbers".
> No, there are no foreign keys, so you are right, it is not possible to
> join tables without keys. (The primary keys in the three are the "id";
> however I think Im not writing it with the standard notation)
>
> On the other hand, the 3 tables got a "date" field. So I should be able
> to link them using this field, shouldn't I? I mean, the 4th of June
> 2005, there are an X amount of rows on each table that has this date in
> their "date" field don't they?
>
>
> shipped ( id, shipdate, sn);
>
> 1 2005-06-03 400250
> 2 2005-06-04 400251
> 3 2005-06-04 400252
> 4 2005-06-04 400253
> 5 2005-06-04 400254
> 6 2005-06-05 400255
>
>
> undelivered (id, undate, sn);
>
> 1 2005-06-03 400220
> 2 2005-06-03 400218
> 3 2005-06-04 400223
> 4 2005-06-04 400229
> 5 2005-06-04 400211
> 6 2005-06-05 400235
>
>
> return (id, redate, sn);
>
> 1 2005-06-03 400160
> 2 2005-06-03 400168
> 3 2005-06-03 400153
> 4 2005-06-04 400219
> 5 2005-06-04 400221
> 6 2005-06-05 400230
>
>
>
>
>
> So, I should be able to get this result:
>
> Date | shipped | undelivered | return
> 2005-06-04 | 4 | 3 | 2
>
>
>
> Because of the common "date" field.
>
> Do you think this is possible? I really need to make this sql work, I
> have spend a lot of time on the internet and I cant make it work, any
> further help would be greately appreciated!
>
> Thanks a lot.
>
> James.
> Dublin, Ireland.
>
hi james,
well i realy would consider your design, but give this one a try:
just use an inner join for this one.
text: on a certain date you want the amount of shipped, undelivered and
returns.
i'll take the date from shipped as the leading date:
select s.date, count(s.id), count(u.id), count(r.id) from shipped s,
undelivered u, return r where s.date=u.date or s.date=r.date group by
s.date;
i would use a different column than of date (for example sdate), because
date is a datatype. the table 'return' i would rename to returned
(return is a reserved word as well).
returned (id, sdate)
undelivered (id, sdate)
shipped (id, sdate)
your query would look like:
select s.sdate, count(s.id),count(r.id),count(u.id) from shipped s,
returned r, undelivered u where s.sdate=r.sdate and s.sdate=u.sdate
group by s.sdate;
Best Regards,
Danny Stolle
Netherlands
EmoeSoft
