title: SQL joins to avoid duplicates in two tables
body: <p>I have two tables, one for images and one for parts. A part has an image, but the parts are duplicated and hence the images are duplicated. I want to download the images just one for a unique part number. </p>
  
  <pre><code>One PartNumber HAS many images (all the same at different locations)
  One PartID HAS one image (unique imageid)
  One ImageID HAS one partnumber
  </code></pre>
  
  <p>I have summarised the tables below:</p>
  
  <pre><code>Part Table: PartNumber, ImageID
  
  Image Table: ImageID, ImageName, ImageSaved (true or false - 1,0)
  </code></pre>
  
  <p>An example data would be: </p>
  
  <p>Parts: (P1,1), (P1, 2), (P1, 3), (P2, 4)<br>
  Images: (1, I1, 1), (2, I2, 0), (3, I3, 0), (4, I4, 0)</p>
  
  <p>Hence, P1 has images I1..3 but they are actually the same image but stored at different locations. Hence once it is downloaded once (imagesaved = 1) the other records for that part may be ignored. </p>
  
  <p>I have tried the following to extract all the images but I need to add something to stop the system downloading all the duplicate images (that is happening now). </p>
  
  <pre><code>select * from trahanimage ti1 inner join  
  (
  select top 10 ti.imageid, trp.manpartnumber, ti.imagename,  
  ti.imageurl,       row_number() 
  over(partition by trp.manpartnumber order by trp.manpartnumber) as rk
  from trahanimage ti
  inner join trahanretailerpart trp 
  on trp.imageid = ti.imageid
  and ti.imagetype = 2
  and ti.imageSaved = 0
  and trp.currentpart = 1
  and trp.shopid = 10 ) as summary on summary.imageid = ti1.imageid
  and summary.rk = 1
  </code></pre>
  
  <p>This works for the first time, but after the download the imagesaved to changed to 1 for one record (eg I1) and then when the next batch runs it needs to ignore the images joined for the part with an image already saved. I think a new join is needed after summary.rk. Logically speaking the logic is</p>
  
  <pre><code>and summary.rk =1 and ti1.imageid not in (select imageid from
  trahanimage where imagesaved = 1 and manpartnumber = part number in
  inner join).
  </code></pre>
  
  <p>Any help is welcome.</p>