I have two tables, one called RMAs and the other called rma_line_items. The
first one has the general details of the RMA (Return Merchandise
Authorization) , the second holds the details of each item being returned.

What I want is a listing of the RMA ids (which are unique in the RMAs table)
which have more than one line item in the corresponding table. So I'm
using:

SELECT * FROM RMAs, rma_line_items
WHERE TO_DAYS(date_settled) = 733274
AND RMAs.rma_id IN
(SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1)

and it's netting me nothing, which I know is not true. So to investigate I
just ran the subselect:

SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1

and I find it's not giving me but one row, the first one to match having
more than one item. But there are plenty more RMAs that have more than one
entry in the rma_line_items table and I need to get at them.

What am I doing wrong? Any ideas?

Mike

Try this:

SELECT RMAs.rma_id FROM RMAs, rma_line_items
WHERE TO_DAYS(date_settled) = 733274
AND RMAs.rma_id = rma_line_items.rma_id
GROUP BY RMAs.rma_id HAVING COUNT(*) > 1

On Sep 10, 2007, at 11:36 PM, Mike Mannakee wrote:

> I have two tables, one called RMAs and the other called
> rma_line_items. The
> first one has the general details of the RMA (Return Merchandise
> Authorization) , the second holds the details of each item being
> returned.
>
> What I want is a listing of the RMA ids (which are unique in the
> RMAs table)
> which have more than one line item in the corresponding table. So I'm
> using:
>
> SELECT * FROM RMAs, rma_line_items
> WHERE TO_DAYS(date_settled) = 733274
> AND RMAs.rma_id IN
> (SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1)
>
> and it's netting me nothing, which I know is not true. So to
> investigate I
> just ran the subselect:
>
> SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1
>
> and I find it's not giving me but one row, the first one to match
> having
> more than one item. But there are plenty more RMAs that have more
> than one
> entry in the rma_line_items table and I need to get at them.
>
> What am I doing wrong? Any ideas?
>
> Mike
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=david@davidsj.com
>
>