vendredi 15 janvier 2016

how to map different rows in join

I am using this query, but this is not giving expected result. I want the result to have unique mapped_id. However, it always returns the 1st match. I tried with DISTINCT also, but to no avail.

SELECT *
FROM
    Item A
INNER JOIN Collection B ON A.item_id=B.item_id
GROUP BY
    A.item_id,
    B.id,
    B.collection_id

Here are my tables:

Collection

id      | collection_id | item_id
a           1               100
b           1               200
c           1               200

Item

item_id | mapped_id
100         abc 
200         def
200         ghi

This is the output with the above query:

collection_id | item_id | mapped_id
1               100         abc
1               200         def
1               200         def

However, my expected result is:

collection_id | item_id | mapped_id
1               100         abc
1               200         def
1               200         ghi

Aucun commentaire:

Enregistrer un commentaire