List:General Discussion« Previous MessageNext Message »
From:Danny Stolle Date:June 16 2005 4:57pm
Subject:Re: help joining tables in a query
View as plain text  
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
Thread
help joining tables in a queryJames M. Gonzalez15 Jun
Re: help joining tables in a queryDanny Stolle16 Jun