I am trying to extract data from an external source where I have a Productmaster table and a Factorymaster table. Products can be made at more than one factory.
simplifying the model
Productmaster contains 2 fields Productcode char(8) and MadebyFactoryKey (int)
Factorymaster contains Factorykey (int) and PlantName char(20)
The sql statement I have is
SELECT DISTINCT P.Productcode,F.PlantName FROM Productmaster P INNER JOIN Factorymaster F ON P.MadeByFactoryKey=F.FactoryKey
However this returns several rows for each product.
The output that I want to achieve is one row for each product and where there are more than one madebyfactory, only show the first value.
Can anyone offer any advice.
Thanks