multiple join query select only one of each type <p>I am a newbie on SQL so please don't hesitate to add other comments about the query.</p> <p>The database i'm working towards is old and is done almost completely without constraints.</p> <p>this is the query i'm working on:</p> <pre><code> select cp.id, cp.connectedId, pd.Name, prt.Data, pd.language, prt.FileName, pr.resourceType from [AAES_PAM_DEV].[dbo].[ConnectedProduct] cp left join [AAES_PAM_DEV].[dbo].[Product] p on cp.connectedId = p.id left join [AAES_PAM_DEV].[dbo].[ProductResource] pr on cp.connectedId = pr.id and cp.Company = pr.Company inner join [AAES_PAM_DEV].[dbo].[ProductDescription] pd on cp.connectedId = pd.id and cp.Company = pd.Company left join [AAES_PAM_DEV].[dbo].[ProductResourceThumbnail] prt on cp.connectedId = prt.ProductId and cp.Company = prt.CompanyName and pr.fileName = prt.FileName where pd.language = 'en' order by cp.id </code></pre> <p>this is result: <a href="https://i.stack.imgur.com/H7SDL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/H7SDL.png" alt="enter image description here"></a></p> <p>what i want to do now is pick rows depending on resourceType (the column to the right)</p> <p>i only want to pick a unique row depending on id, connectedId and language. I also want it to pick this row by looking if there is a resourcetype with the type "I" first and if there is none of that then pick where resourcetype is "S" and if none of those exist then just take the first row it finds. </p> <p>here is an example of what i want it too look:</p> <p><a href="https://i.stack.imgur.com/ps1wO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ps1wO.png" alt="enter image description here"></a></p> <p>As you can see it is a unique row on the combined key (id, connectedId and language) and also it selects only one row where the resourcetypeid = "I". So even if there is a lot of other rows with the same combined key i want it to select one row where first the resourcetypeid = "I" and if that not exists then i want it to take the row where resourcetypeid = "S" and if that not exists it can take the first row it finds with the combined key(id, connectedId and language)</p> <p>My try:</p> <pre><code>SELECT cp.id, cp.connectedId, pd.language, pr.resourceType FROM ( SELECT cp.id, cp.connectedId, pd.language, pr.resourceType , DENSE_RANK() OVER(ORDER BY [Priority]) AS [RANK] FROM ( SELECT cp.id, cp.connectedId, pd.language, pr.resourceType , CASE pr.resourceType WHEN 'I' THEN 1 WHEN 'S' THEN 2 ELSE 3 END AS [Priority] FROM [AAES_PAM_DEV].[dbo].[ConnectedProduct] cp left join [AAES_PAM_DEV].[dbo].[Product] p on cp.connectedId = p.id left join [AAES_PAM_DEV].[dbo].[ProductResource] pr on cp.connectedId = pr.id and cp.Company = pr.Company inner join [AAES_PAM_DEV].[dbo].[ProductDescription] pd on cp.connectedId = pd.id and cp.Company = pd.Company left join [AAES_PAM_DEV].[dbo].[ProductResourceThumbnail] prt on cp.connectedId = prt.ProductId and cp.Company = prt.CompanyName and pr.fileName = prt.FileName WHERE pd.language = 'en' GROUP BY cp.id, cp.connectedId, pd.language, pr.resourceType ) AS PrioritizedSelect ) AS RankedSelect WHERE [RANK] = 1 </code></pre> <p>this is the errors i get:</p> <p>Msg 4104, Level 16, State 1, Line 53 The multi-part identifier "cp.id" could not be bound. Msg 4104, Level 16, State 1, Line 53 The multi-part identifier "cp.connectedId" could not be bound. Msg 4104, Level 16, State 1, Line 53 The multi-part identifier "pd.language" could not be bound. Msg 4104, Level 16, State 1, Line 53 The multi-part identifier "pr.resourceType" could not be bound. Msg 4104, Level 16, State 1, Line 51 The multi-part identifier "cp.id" could not be bound. Msg 4104, Level 16, State 1, Line 51 The multi-part identifier "cp.connectedId" could not be bound. Msg 4104, Level 16, State 1, Line 51 The multi-part identifier "pd.language" could not be bound. Msg 4104, Level 16, State 1, Line 51 The multi-part identifier "pr.resourceType" could not be bound.</p>