CODEWITHSUNDEEP
sqlsql-server
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

SQL join with duplicate values

I realized I made an error in my last post, this should be more clear.

Say I have a table with IDs and current IDs:

tbl_id
id   cur_id   value
aa    zz        5
zz    zz        10
bb    yy        2
yy    yy        11

Say I have another table with prices:

tbl_price
id    price
aa      200
bb      100

I need to return the price and value of the cur_id associated with each id in tbl_price:

cur_id   value   price
zz        10       200
yy        11       100

The issue I'm having is returning the correct value. When I do an inner join:

SELECT
  tbl_id.cur_id,
  tbl_id.value,
  tbl_price.price
FROM
  tbl_price
INNER JOIN tbl_id
  ON tbl_id.id = tbl_price.id

This gives me these results:

cur_id   value   price
zz        5       200
yy        2       100

I get why this happens because of the way the join works. However, I am not sure how to then re-join the table again to get the appropriate value where tbl_id.id = tbl_id.cur_id. Do I simply re-join the table again someway?

Upvotes: 1

Views: 116

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You need one more self join to get the results that you want:

SELECT
  i1.cur_id,
  i2.value,
  p.price
FROM
  tbl_price p
INNER JOIN tbl_id i1 ON i1.id = p.id
INNER JOIN tbl_id i2 ON i1.cur_id = i2.id

The idea is to join the table of IDs again, and grab the one-away IDs from that second join.

Demo on SQLFiddle.

Upvotes: 2

charlesw
charlesw

Reputation: 612

Try adding a where statement:

INNER JOIN tbl_id
  ON tbl_id.id = tbl_price.id 
  WHERE tbl_id.id = tbl_id.cur_id

Upvotes: 0

Related Questions