find distinct records from two table

Question

Table Product PID(PK) Name 1 Mobile 2 Laptop 3 Fashion Table Coupons ID PID City 1 2 Bangalore 2 3 Pune 3 2 Mumbai 4 1 Bangalore 5 2 Mumbai I want to select distinct City name records joining two tables (if i have five different city then its one one record from each city). Pl'z suggest me, how can i fetch this records. I trired using distinct keyword to fetch records but i am unable to find desire output....

Hemant Srivastava · Answer

so then , you need to use: SELECT DISTINCT C.City, C.PID, P.Name FROM Product AS P INNER JOIN Coupons AS C ON C.PID = P.PID ORDER BY C.City ____________________________________ City PID Name ____________________________________ Banglore 1 Mobile Banglore 2 Laptop Mumbai 2 Laptop Pune 3 Fashion So, in this way you get rid of the one duplicate row "Mumbai 2 Laptop" in comparison to previous result. You can't get rid of Banglore column duplicacy because full row wise "Banglore 1 Mobile" and "Banglore 1 Laptop" are different. As Jignesh said " Distinct can not be applied to only few columns it is always applied to whole column set means all column which are used with "select". " Hope it would help you.

Sumit Kumar Sinha · Answer

"Hemant Srivastava" I want to distinct records city wise. Eg: City PID Product Bangalore 2 Laptop

Jignesh Trivedi · Answer

hi, The SQL DISTINCT clause allows you to remove duplicates from the result set. The SQL DISTINCT clause can only be used with SQL SELECT statements. so Distinct can not be applied to only few columns it is always applied to whole column set means all column which are used with "select". try following two query, it return different result set. select Distinct City,P.Name from #product p join #Coupons c on c.PID = p.PID select Distinct * from #product p join #Coupons c on c.PID = p.PID hope this will help you.

Hemant Srivastava · Answer

If you use following query: SELECT C.City, C.PID, P.Name FROM Product AS P INNER JOIN Coupons AS C ON C.PID = P.PID ORDER BY C.City you would get result as; __________________________________ City PID Name __________________________________ Banglore 2 Laptop Banglore 1 Mobile Mumbai 2 Laptop Mumbai 2 Laptop Pune 3 Fashion Now what you want?

Anurag Sarkar · Answer

Please show me some code that you have tried. Which controls you are using? And which platform you are working windows or asp.net.?

Sumit Kumar Sinha · Answer

i want to select distinct city name, PID and Name from both table(menas for every distinct city i want to PID and Name)

Anurag Sarkar · Answer

You should use joining for selecting records from distinct records. and if you want the query please show me the output show that i can make the query for you.

find distinct records from two table

Answers (7)