How do I join duplicate columns

Question

0.00/5 (No votes)

See more:

XML

Hello,

I have two tables named description and price with same duplicate ids and model.

Description table:
ID  DESCR MODEL
  1  tyre   A
  2  tyre   A
  1  tube   A
  1  bumper A
  2  pipe   A

Price table:
ID Price MODEL
  1   54    A
  1   23    A
  1   35    A
  2   55    A
  2   35    A

I want to join like this:

ID  DESCR Price MODEL
  1   tyre  54   A
  1   tube  23   A
  1   bumper 35  A
  2   tyre   55  A
  2   pipe   35  A

I tried with inner join but several combinations of duplicates comes as a result. Is there any way to handle this?

Thanks in advance.

Posted 18-Mar-15 4:23am

priyae

Add a Solution

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)

Peter Leow · Accepted Answer · 2015-03-18T05:17:00

Solution 1

Try this:

SQL

select d.id, d.descr, p.price, d.model from
(
select id,  descr, model,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY descr DESC) row
from description
) d
inner join
(select id,  price,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) row
from price
 ) p
 on d.row=p.row where d.id=p.id

Posted 18-Mar-15 5:17am

Peter Leow

Comments

priyae 19-Mar-15 6:08am

Thank you very much Peter:)
I tried your code and got the expected solution.
It Helped me a lot.

Regards,
priya

Aner Bautista · Accepted Answer · 2015-03-18T06:42:00

Hello

I see an issue with the definition of the primary key.

I recommend you to take a look at this:
http://en.wikipedia.org/wiki/Unique_key[^]

I recommend you to define a single field Primary key with autoincrement (if you decide to use a numeric field), and if you want to constraint some kind of field combination, I recommend you to use Unique Key constraint (additional to Primary Key).

When you define the primary or unique key, you will be able to reference the table on another table.

I'd like to see your Data Dictionary to see how you define and use this fields and how you constraint them.

If you still have any other question, please fell free to ask.

Best Regards.
Aner