471,092 Members | 1,495 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

having mySQL find only orders with multiple rows in another table

This is a tough question to ask.
I need to pull up a report of all the orders in our e-commerce site,
that has more than one item attributed to it.
orders.ordernum, orderitems.itemnumber orderitems.ordernum is shared
with the same field in the orders table.

In PHP I could just do a querey of all ordernumbers and within the
WHILE do a querey of orderitems where the ordernum equals the original
querey, and then do what I need IF numrows > 1.

But surely there's a way to maybe do that in the mySQL querey? Some way
that might be more efficient than a querey of EVERY order?

If there's simply a name of some topic in mySQL that might apply, just
tell me to look that up. For example: "Take a look for 'distinct',
newbie." =)

Thanks for any help!
Liam

Jul 23 '05 #1
2 1782
On Wed, 13 Jul 2005 10:34:07 -0700, news wrote:
This is a tough question to ask.
I need to pull up a report of all the orders in our e-commerce site,
that has more than one item attributed to it. orders.ordernum,
orderitems.itemnumber orderitems.ordernum is shared with the same field
in the orders table.

But surely there's a way to maybe do that in the mySQL querey? Some way
that might be more efficient than a querey of EVERY order?

Don't know why that should be tough to ask. If I understand it correctly,
it's easy enough to answer. To get a simple list of order numbers that are
associated with more than 1 single item, I would suggest -

SELECT ordernum
FROM orderitems
WHERE COUNT(*) > 1
GROUP BY ordernum;
In PHP I could just do a querey of all ordernumbers and within the WHILE
do a querey of orderitems where the ordernum equals the original querey,
and then do what I need IF numrows > 1.
I'm sure that works fine. But I would hazard a beer bet that says the
MySQL engine, when queried as shown, will deliver the answer substantially
faster than your PHP program loop.

And

If MySQL is running on a separate server - using the PHP code you mention
requires that the server push all records down the network pipes just so
your PHP code can examine the data further and subselect the order numbers
you seek. Use of the SELECT query shown asks the server to deliver only
the data you want. IOW - putting the task to MySQL saves bandwith and is
more network friendly.
If there's simply a name of some topic in mySQL that might apply, just
tell me to look that up. For example: "Take a look for 'distinct',
newbie." =)


You would probably like to become more familiar with SQL "Aggregate
Functions". Of these, the function COUNT() is probably the most used and
useful.

Although you will doubtless discover more that will make you happy :-)
Thomas Bartkus

Jul 23 '05 #2
Thomas Bartkus wrote:
On Wed, 13 Jul 2005 10:34:07 -0700, news wrote:
This is a tough question to ask.
I need to pull up a report of all the orders in our e-commerce site,
that has more than one item attributed to it. orders.ordernum,
orderitems.itemnumber orderitems.ordernum is shared with the same field
in the orders table.

But surely there's a way to maybe do that in the mySQL querey? Some way
that might be more efficient than a querey of EVERY order?

Don't know why that should be tough to ask. If I understand it correctly,
it's easy enough to answer. To get a simple list of order numbers that are
associated with more than 1 single item, I would suggest -

SELECT ordernum
FROM orderitems
WHERE COUNT(*) > 1
GROUP BY ordernum;

--snip-- You would probably like to become more familiar with SQL "Aggregate
Functions". Of these, the function COUNT() is probably the most used and
useful.

Although you will doubtless discover more that will make you happy :-)


Thanks for the help!
That querey as written kept giving me a "invalid use of group function"
error, but I did research into COUNT and GROUP BY thanks to you, and
came up with:

SELECT ordernum, COUNT(ordernum)
FROM orderscart
GROUP BY ordernum
HAVING COUNT(ordernum)>1;

and that worked perfectly. I'm seeing a lot of cool and powerful things
I can do with queries like this. =)

Thanks again!!
Liam

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Gary | last post: by
6 posts views Thread by jacob nikom | last post: by
2 posts views Thread by Christian Höhener | last post: by
4 posts views Thread by Federico | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.