fetch distinct value from two tables

Question

5.00/5 (1 vote)

See more:

able Product

PID(PK) --- Name --- ImagUrl

1 - Mobile --- something
2 - Laptop --- something
3 - Fashion --- something

Tabe 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 i want to select 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....

Posted 15-Jan-13 0:18am

Sumit_Kumar_Sinha

Add a Solution

Comments

Herman<T>.Instance 15-Jan-13 6:24am

In fact your determantion of tables is incorrect. Create A table City.

Suvabrata Roy 16-Jan-13 5:04am

which columns are required in your output ...?

Sumit_Kumar_Sinha 17-Jan-13 0:15am

for distinct city PID and Name

1 solution

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)

Herman<T>.Instance · Answer 1 · 2013-01-15T00:25:00

Solution 1

SQL

select distinct Sumit.City, Sumit.Name  -- or Sumit.PID, depends on what you want to show
from
(
    select Row_Number() over(Partition By c.ID, c.City Order BY c.ID desc, City) as rowno,
	c.ID, c.City, p.Name, p.PID
    From Coupons  c
    JOIN Product p on c.pid = p.pid
) as Sumit
where Sumit.rowno = 1

1 assumption is that you use Sql Server by Microsoft

Posted 15-Jan-13 0:25am

Herman<T>.Instance

Updated 15-Jan-13 22:58pm

v4

Comments

Sumit_Kumar_Sinha 15-Jan-13 6:36am

hey....i want to select city name and last Inserted PID for each city ........

Herman<T>.Instance 15-Jan-13 6:57am

Do you need PID or NAME in the final result?

Sumit_Kumar_Sinha 15-Jan-13 9:34am

i need city and pid in the final result

Herman<T>.Instance 15-Jan-13 9:42am

the query above is already changed. in stead of sumit.Name you stat sumit.PID. Have you tested the query?

Sumit_Kumar_Sinha 16-Jan-13 1:25am

it's throwing error :- Invalid column name 'City'.

Herman<T>.Instance 16-Jan-13 4:58am

your RIGHT. It is from Coupons in stead of From City

Sumit_Kumar_Sinha 17-Jan-13 0:16am

same error is coming....................

Herman<T>.Instance 17-Jan-13 3:50am

strange. I used the column names you gave in your question. What are the real columnnames?

Sumit_Kumar_Sinha 21-Jan-13 2:12am

with CTE as
(
select CouponsStock.CouponsID, ProdSubCatItemsDescription.Items_Desc_ShortDescription,
ROW_NUMBER() over(PARTITION BY CouponsStock.CouponsID, ProdSubCatItemsDescription.Items_Desc_ShortDescription order by ProdSubCatItemsDescription.Items_Desc_ShortDescription )as Cnt
from category inner join ProductCategory on
category.Cat_ID=ProductCategory.Cat_ID
inner join ProductSubCategory on ProductCategory.Prod_Cat_ID=ProductSubCategory.Prod_Cat_ID
inner join ProductSubCatItmes on ProductSubCategory.Prod_Sub_Cat_ID=ProductSubCatItmes.Prod_Sub_Cat_ID
inner join ProdSubCatItemsDescription on ProductSubCatItmes.Prod_Sub_Cat_Items_ID=ProdSubCatItemsDescription.Prod_Sub_Cat_Items_ID
inner join CouponsStock on ProdSubCatItemsDescription.Prod_Sub_Cat_Items_Desc_ID=CouponsStock.Prod_Sub_Cat_Items_Desc_ID
where category.Cat_Name='Fashion'
and ProdSubCatItemsDescription.Items_Desc_Status<>'Disabled'
and CouponsStock.CouponsEndDate>=GETDATE()
and CouponsStock.CouponsStatus='Visible'
)
select CouponsID, Items_Desc_ShortDescription
from CTE where Cnt=1

Sumit_Kumar_Sinha 21-Jan-13 2:13am

16 KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )
17 KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )
39 Crosscreek Fil-A-Fil Casual Shirt
46 Live Life Colorful : Western Wear.....
47 50% Off For Select Time Period

Sumit_Kumar_Sinha 21-Jan-13 2:14am

the above is output........i want to select the distinct reocrds because "KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )" title repeated two times in the output

Herman<T>.Instance 21-Jan-13 6:38am

In your CTE no columns with name City are defined, that is why my query fails. Add city to your CTE or see that your Row_number function lead to unique 'distinct' rows