duplicate query

[josephjoe]

i need to run a query that show all the records that do not contact a duplicate value in one field.


Example: Batch_No. I need to see a query where the results are only the records with no duplicate batch_no.

Table
No. Product
--- ------
123 - Glue
145 - Paint
123 - Glue
178 - Nails
201 - Hammer
123 - Glue
178 - Nails

Query Report I'm looking for
No. Product
--- ------
145 - Paint
201 - Hammer

 

[alexjones]

Something like

select t.no, t.product
  from table as t
  join (select no
          from table
         group by no having count(*) = 1) as n
   on t.no = n.no

perhaps?

 

[LNBruno]

SELECT no, product
FROM #Table
GROUP BY no, product
HAVING COUNT(no) = 1

perhaps?

< M!ke >
I am not a hamster and life is not a wheel.

 

[Dashley]

Or

Select no, product
from table
group by no, product
having count(no) =1