Hey, I'm not that good at writing sql and I've gotten stuck trying to figure out a way to find rows with duplicate values and returning more data than just the duplicates.
I'm sorry, I didn't get the ctrl+K code formatting to work either...
So the picture below is a pretend data set based on what I need to produce. I'm looking for a way to find all rows with duplicates in eic.itmcod and return a few other things from those rows. From the example picture I want the result to only show row 2,3, 5 and 6 where there are duplicates in eic.itmcod. I have tried Group by X Having count(eic.itmcod)>1 but I can't seem to get that to work for some reason. Excuse my shit code, I hope my question is understandable and have a nice day!
SELECT sup.supnam 'supplier',eic.extitm 'Suppliers Code', eic.itmcod 'Internal Code',itm.itmnam 'Name', eic.extunt 'Unit',eic.extrra 'Amount',itm.basunt 'Base Unit', eic.curprc 'Price',eic.agrnum 'Agreement Number', eic.agvunt as 'Valid Until', CASE WHEN eic.extcod=itm.supcod THEN 'Y' ELSE 'N' END 'Main Supplier'
FROM sup, itm, eic
WHERE itm.compny=eic.compny AND itm.itmcod=eic.itmcod AND eic.extcod=sup.supcod
ORDER BY eic.itmcod