How to Get unique row from the multiple duplicate row value

Question

0.00/5 (No votes)

See more:

C#

Id	Cat_Id	P_Name	                         Image_Path
1040	1010	aaaaaaa        	     /images/products/NewImages/Gamo-Whisper-
1040	1010	aaaaaaa        	     /images/products/NewImages/IMG_0401.png
1040	1010	aaaaaaa        	     /images/products/NewImages/IMG_0402.png
1040	1010	aaaaaaa        	     /images/products/NewImages/gamo-silent-
1040	1010	aaaaaaa        	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0140a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0135a.jpg
1041	1010	bbbbbbbbbbb	     /images/products/NewImages/SMALL_IMG_0140a.jpg
1042	1011	ccccccccccc	     /images/products/NewImages/IMG_0413.png

From this table i want to fetch data as per the Id Column Those have unique Id like
1040,1041,1042 only
The Image_Path is filed of another table named as ImagePathTable.I am using Inner Join to get this values
Table2-ImagePathTable

C#

Id	P_Id	ImagePath
1041	1040	/images/products/NewImages/IMG_0401.png
1042	1040	/images/products/NewImages/IMG_0402.png
1043	1040	/images/products/NewImages/detail_whisper_g2.jpg
1044	1040	/images/products/NewImages/gamo-silent-stalker-whisper-igt-22-cal-air-
1045	1041	/images/products/NewImages/SMALL_IMG_0135a.jpg
1046	1041	/images/products/NewImages/SMALL_IMG_0140a.jpg
1047	1041	/images/products/NewImages/SMALL_IMG_0135a.jpg
1048	1041	/images/products/NewImages/SMALL_IMG_0140a.jpg
1049	1040	/images/products/NewImages/Gamo-Whisper-CFR-Fixed-Barrel_GA-
1050	1042	/images/products/NewImages/IMG_0413.png

Table1-Products

C#

Id	P_Name	                       Cat_Id
1040	aaaaaaaaaa	                1010
1041	aaaaaaaaaa		          1010
1042	aaaaaaaaaa		        1010
1043	aaaaaaaaaa		        1010
1044	aaaaaaaaaa	                1010
1045	bbbbbbbbbb	                1010
1046	bbbbbbbbbb	                1010
1047	cccccccccc	                1011
1048	cccccccccc	        	1011
1049	cccccccccc	                1011

According to the name of the Product in product table there is multiple images uploaded in the table 2 "ImagePathTable"

Posted 30-Nov-15 20:59pm

@shok kumar mishra

Updated 30-Nov-15 21:59pm

v3

Add a Solution

Comments

Tomas Takac 1-Dec-15 3:04am

I don't understand what's the problem here. Did you try: SELECT DISTINCT Id FROM table.

@shok kumar mishra 1-Dec-15 3:49am

I want to fetch data having Unique Id in the tables.
The Image_Path is Column of another table named as ImagePathTable.I am using Inner Join to get this values

Tomas Takac 1-Dec-15 4:58am

Fair enough but I still don't understand what are you trying to do. What should be unique. Please update your question with your expected results.

@shok kumar mishra 1-Dec-15 5:11am

Check the top table Id there are some multiple id is there i want to fetch unique Id values from the table

_Asif_ 1-Dec-15 5:32am

It would get easy for us if you could show your desired output in table form.

@shok kumar mishra 1-Dec-15 5:41am

<pre lang="C#">
Id Cat_Id P_Name Image_Path
1040 1010 aaaaaaa /images/products/NewImages/Gamo-Whiser
1041 1010 bbbbbbbbbbb images/products/NewImages/SMALL_IMG_0135a.jpg
1042 1011 ccccccccccc /images/products/NewImages/IMG_0413.png
</pre>

_Asif_ 1-Dec-15 5:49am

For 1041 case should not it be like? becasue 1048 id is the latest udpate?
1041 1010 bbbbbbbbbbb SMALL_IMG_0140a.jpg

@shok kumar mishra 1-Dec-15 5:53am

On the basis of the 1040 id i am storing multiple images u can check second table up there.
From top table which is listed above all the table.I want to show only those values whose Id is not repeated like in above ID(1040) have multiple row i want to show only 1 row from multiple row.

2 solutions

Solution 2

in your case DISTINCT will not work.
According to your requirement i think this will work for you.

SQL

SELECT A.Id, A.P_Name, B.ImagePath
FROM Product A
INNER JOIN (SELECT P_Id,MIN(ImagePath)  ImagePath FROM img GROUP BY P_Id) AS B
ON B.P_Id = A.ID

Posted 30-Nov-15 23:30pm

Jawad Ahmed Tanoli

Comments

_Asif_ 1-Dec-15 5:37am

This is not a good way in doing modification to your actual solution. You need to select "Improve Solution" of Solution 1 and make changes in it instead of adding new solution as Solution 2 and don't forget to remove Solution 2

Jawad Ahmed Tanoli 1-Dec-15 5:42am

ok i will remove old solution thanks for suggestion

@shok kumar mishra 1-Dec-15 5:50am

Imagepath Column is field of ProductImagePath Table
after using thisinner join i am getting the top table value.
From top table which is listed above all the table.I want to show only those values whose Id is not repeated like in above ID(1040) have multiple row i want to show only 1 row from multiple row.Plz find out the solution for this problem
select p.Id,p.Cat_Id,p.P_Name,img.ImagePath from P_Product p INNER JOIN P_ImagePath img ON p.Id=img.P_Id

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)

_Asif_ · Accepted Answer · 2015-11-30T23:58:00

Solution 3

Try this,

SQL

SELECT t1.P_ID, P.CAT_ID, P.P_NAME, IPT.IMAGEPATH
FROM 
(SELECT P_ID, MAX(ID) AS MAX_ID
FROM   ImagePathTable
GROUP BY P_ID
) T1 INNER JOIN ImagePathTable IPT on T1.MAX_ID = IPT.ID
INNER JOIN PRODUCT P ON T1.P_ID = P.ID

Posted 30-Nov-15 23:58pm

_Asif_

Updated 1-Dec-15 0:32am

v2

Comments

@shok kumar mishra 1-Dec-15 6:08am

I am getting error

@shok kumar mishra 1-Dec-15 6:11am

Can u explain these code please

_Asif_ 1-Dec-15 6:29am

What error are you getting?

@shok kumar mishra 1-Dec-15 6:30am

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'T1'.

_Asif_ 1-Dec-15 6:32am

I have modified the solution, there was syntax error in the SQL. Since i dont have the structure thats why not able to run the sql :)

@shok kumar mishra 1-Dec-15 6:34am

can i send u the whole database so u can easily catch the errors

_Asif_ 1-Dec-15 6:42am

Unfortunately I can't, secondly you already have found the solution so explore it by yourself.

@shok kumar mishra 1-Dec-15 6:43am

ok thank u