Hoe to make a selected column distinct in inner join

Question

I need to make one column distinct in inner join three tables I need to make videoName distinct y only not other colunms how can I write the query please help SElECT distinct t1.MID distinct t1.VideoName t2.*t3.UniIDt3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation As t2 ON t2.MID=t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID=t2.UId where t3.user_type= 'Staff' and t1.IsDeleted = 0 AND t2.MID= 'CM1034D' ; thanks

Midhun T P · Answer

Hi Joma As per my understandings there is no way you getting duplicate values in the videoname column for below query. There may be some difference in values - select max(MID) as MIDvideoname from dbo.Material group by Videoname

Tapan Patel · Answer

Basic query should be something like this. SELECT columns FROM TABLE INNER JOIN ( SELECT DISTINCT columns FROM Table2) AS Alias ON TABLE . COLUMN =Alias. Column Please mark it as an answer if it addresses the issue.

Midhun T P · Answer

Hi May be we have to try another way for that. Actually why do you require such a data with unique value for that particular column only?

Joma Rajab · Answer

yes Midhun you are right but I am sorry there are still rows duplicated as I just copied and pasted your query

Joma Rajab · Answer

Yes Midhun It still having repeated value any new idea please I appreciate your help Thanks

Midhun T P · Answer

Hi What is the result you getting while running below query? Is the videoname column having only distinct values? select max(MID) as MIDvideoname from dbo.Material group by Videoname

Tapan Patel · Answer

Probably if you want that kind of feature please take a look at Partition Over . https://msdn.microsoft.com/en-us/library/ms189461.aspx

Joma Rajab · Answer

thanks guys still duplication in the column name I could not make it distinct with your code Midhun T P thanks

Midhun T P · Answer

Hi Joma From the above query you provided I think that this is not possible to achieve what you are trying. You may need to find some other way to address this issue. Assuming that MID is the primary key in the table It is not possible to get distinct records for only a particular column in that table using above query. Try below query. I'm not sure it solves your issue. Still you can give a try - SElECT distinct t1.MID t4.VideoName t2.*t3.UniIDt3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation As t2 ON t2.MID=t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID=t2.UId INNER JOIN (select max(MID) as MIDvideoname from dbo.Material group by Videoname) AS t4 ON t1.MID=t4.MId where t3.user_type='Staff' and t1.IsDeleted = 0 AND t2.MID='CM1034D';