How to make distinct for value of one column in SQL query with inner join

Question

0.00/5 (No votes)

See more:

I need to make one column distinct in inner join three tables
I need to make videoName distinct only not other columns
how can I write the query

please help

What I have tried:

SElECT distinct t1.MID, distinct t1.VideoName, t2.*,t3.UniID,t3.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';

and this
SElECT distinct t1.MID, t4.VideoName, t2.*,t3.UniID,t3.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 MID,videoname 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';

but video name still duplicated

Posted 16-Aug-16 23:20pm

Member 12618369

Updated 25-Aug-16 0:56am

v2

Add a Solution

Comments

Maciej Los 17-Aug-16 5:52am

What is input and expected output?

Member 12618369 17-Aug-16 6:00am

what do mean bu input the output is values of selected columns but one column has repeated value I need to make it distinct

Aless Alessio 17-Aug-16 11:57am

first: for god's sake, use a Sql Formatter http://www.dpriver.com/pp/sqlformat.htm your SQL is unreadable.
second: you need to tell us the tables structure and the relations between them, otherwise how can we know how to write the query?

Member 12618369 17-Aug-16 13:04pm

the structure of tables are
Materials[id, videoName,MID(forign key),UID,ISPosted]
ModuleRelation[id, Uid(foring key),MID(forign key)]
users[Uid,user_type]
please help

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11114915 - Tanvi · Answer 1 · 2016-08-24T21:56:00

Solution 1

Distinct in such condition will work when only one column i.e. 'VideoName' is selected. If distinct 'VideoName' is required along with other columns then it will bring distinct combination of all the columns instead of distinct of one column.

Posted 24-Aug-16 21:56pm

Member 11114915 - Tanvi

The Praveen Singh · Answer 2 · 2016-08-25T00:56:00

Use group by Instead of Distinct:_

SQL

SElECT t1.MID, t1.VideoName, t2.*,t3.UniID,t3.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' Group by t1.VideoName;