CODEWITHSUNDEEP
sql-server-2008duplicatesinner-join
Michael Sobczak
Michael Sobczak

Reputation: 1085

SQL: issue joining tables

I have the following two tables:

attributevalueassign

itm_id  attr_value_id   attr_id
-----   -------------   -------
396     237             1
396     20              2
715     274             1
715     16              2

attributevalue

attr_id   attr_value_id     attr_value
-------   -------------     ----------
1         237               3055020
1         274               2454518
2         16                Dunlop
2         20                Vogue

I need to be able to write a query that when I supply the item_id (ex:396), I get back the following result:

itm_id  attr_value_id   attr_id   attr_value
------  -------------   -------   ----------
396     237             1         3055020
396     20              2         Vogue

When I try using INNER JOIN, I get more rows than I want:

SELECT     dbo.select_attributevalue.attr_value, dbo.select_attributevalueassign.itm_id
FROM         dbo.select_attributevalueassign INNER JOIN
                      dbo.select_attributevalue ON dbo.select_attributevalueassign.attr_id = dbo.select_attributevalue.attr_id
WHERE     (dbo.select_attributevalueassign.itm_id = 396)

itm_id  attr_value_id   attr_id  attr_value
------  -------------   -------  ----------
396     237             1        3055020
396     237             1        2454518
396     20              2        Dunlop
396     20              2        Vogue

So, my thought is that I probably shouldn't be using a JOIN, but I don't know what the alternative is. I've dug around on this site looking for a solution, and there are many questions on how to remove duplicate records using a join, but I didn't see one similar to what I was asking. My knowledge of SQL queries is basic, so I'm not sure what to do. Any help would be much appreciated!

Upvotes: 1

Views: 26

Answers (1)

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4236

You might need to do inner join by two columns attr_value_id and attr_id. Try this query:

SELECT     dbo.select_attributevalue.attr_value, dbo.select_attributevalueassign.itm_id
FROM         dbo.select_attributevalueassign 
    INNER JOIN dbo.select_attributevalue ON 
    dbo.select_attributevalueassign.attr_id = dbo.select_attributevalue.attr_id
    AND dbo.select_attributevalueassign.attr_value_id = dbo.select_attributevalue.attr_value_id
WHERE     (dbo.select_attributevalueassign.itm_id = 396)

Upvotes: 2

Related Questions