James M. Gonzalez wrote:
-----Original Message-----
From: Danny Stolle [mailto:[EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
