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
>
>