Ugh... got ya. Good point. Well, I'm stuck here on VFP 6.0 SP5, but at any point did Fox introduce the ability to use a select statement in your from clause, as I have done in SQL 2000? That would certainly do the job.

>Nice try, but the "i.code in (select code,count(1)" not work because you're trying to compare 1 column in 2 columns. Also having without grouping returns only 1 row.
>
>not work but Thanks,
>
>>I think so (without testing). Your query with the 'count(1)' needs to use >the 'HAVING' SQL-SELECT clause:
>>select code,count(1) as total from query HAVING total > 1
>>So then you can select * from your first query where code in (new second >query)
>>select i.code,i.name,l.vendor,l.price from list,item where i.code=l.code >and item.code in (select code,count(1) as total from query HAVING total > 1)
>>Something like that, right? As I said, I didn't test any of this because I >did not mock up any data, but I think this at least gets you in the right >direction.
>>>Hi all, i need an sql to get the list of items that have 2 or more price >>but not wich have only 1 price. I'm doing this: select >>i.code,i.name,l.vendor,l.price from list,item where i.code=l.code , this >>sql returns all items with all prices in list but i need only the items >>that have more than 1 record in list, so i make another sql to have the >>code: select code,count(1) as total from query, next i get the code for >>total=1 and delete them in first sql. There is a way to doit with just 1 >>line in sql?
>>>
>>>Thanks in advance,