having mySQL find only orders with multiple rows in another table

[news]

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

[Thomas Bartkus]

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

[news]

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