>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.
In other words, you want to preserve the orders table, which means an
outer join.
FROM (orders o LEFT JOIN accentordetail a ON o.id = a.id)
LEFT JOIN planordermain p ON a.id = p.id
This replaces both your FROM and WHERE clauses. (If you use the
aliases you'll have to use them in the rest of the SQL statement
also.)
At first blush, the Count() problem looks like a data problem. Being
lazy, I'm not inclined to set up a database and try to duplicate the
problem when I don't see a problem with the SQL. I'm not saying the
problem isn't in the SQL, I'm just saying that I'm lazy. If you
posted the data in the tables that resulted in the output above, I'd
be more inclined to proceed to the next step. In general, it makes it
easier for people on the list to help you if we have both the input
and the output.
It's possible that the outer joins will solve the Count() problem,
but I can't tell without the input data.
>Thanks for the input.
>---------------------
>Johnny Withers
>[EMAIL PROTECTED]
>p. 601.853.0211
>c. 601.954.9133
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
