Hi,
I have these 3 tables which hold order information
for a web site.
One table holds the shipping info/credit cart stuff,
another one holds one type of item ('home accents'),
and the other one holds house plans.
When someone orders these items, due to the options on
house plans, I must store the order information for
plans and accents in 2 seperate tables. So, I broke
the order info down into the 3 tables described above.
Each of the accentorders and planorders tables have
an order_id field which coresponds to the ID field
in the mainorder table (which holds the shipping and
CC info).
All this works fine, however I'm stumped on one issue.
I want to use one select statement and get all the items
for that one order AND figure out how many 'Plans' that
order has and how many 'Accents' the order has.
I've tried this select statement:
SELECT orders.id,orders.ship_co_name,orders.ship_name,orders.order_date,
orders.order_total,orders.order_complete,orders.bad,
COUNT(DISTINCT planordermain.id) AS plans,COUNT(DISTINCT accentorderdetail.id) AS
accents
FROM orders,accentorderdetail,planordermain
WHERE ((orders.id=accentorderdetail.id) AND (orders.id=planordermain.order_id))
GROUP BY orders.id
ORDER BY orders.order_date ASC;
Which gives:
+----+--------------+----------------+------------+-------------+----------------+-----+-------+---------+
| id | ship_co_name | ship_name | order_date | order_total | order_complete | bad
|| plans | accents |
+----+--------------+----------------+------------+-------------+----------------+-----+-------+---------+
| 1 | Mindbender | Johnny Withers | 3/20/2001 | 1426.85 | 0 | 1
|| 2 | 1 |
| 2 | Mindbender | Johnny Withers | 3/21/2001 | 1538.13 | 0 | 1
|| 2 | 1 |
| 3 | Mindbender | John Doe | 3/21/2001 | 2084.13 | 0 | 1
|| 3 | 1 |
+----+--------------+----------------+------------+-------------+----------------+-----+-------+---------+
It gives correct results for 'plans' but incorrect results for accents.
Also, if a user doesn't order any 'plans' but orders 'accents' this query
will not return any results at all (because of the AND in the where).
I tried to use OR, but then I get (orders*plans_ordered*accent_orders) which
is not right either.
Thanks for the input.
---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.954.9133
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
