CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

Getting back multiple lookup fields with JOIN

My table contract looks like this:

enter image description here

The item_id here is a primary key which is used as a foreign one on Contract_attr:

enter image description here

I want to return 2 columns one is the contract# (0000000903) and each of the ATTR_Val for field_id = 238 and field_id = 326 (N, N).

This is what I have so far:

SELECT distinct [CONTRACT].item_name, [CONTRACT_ATTR].ATTR_VAL
FROM [dbo].[CONTRACT] 
JOIN [CONTRACT_ATTR]
ON [CONTRACT].item_name = [CONTRACT_ATTR].ATTR_VAL AND [CONTRACT_ATTR].item_id = 17

The 17 will be dynamic in practice. But with this query I am getting back the contract# two times:

enter image description here

Edit: also If I add AND [CONTRACT_ATTR].field_id in (238, 326) I get back nothing.

What am I doing wrong and how do I fix it?

Upvotes: 1

Views: 25

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

Can you change the fields in the ON clause 

SELECT DISTINCT CO.item_name, CA.ATTR_VAL
FROM [dbo].[CONTRACT] CO
JOIN [CONTRACT_ATTR] CA ON CA.Item_Id = CO.Item_Id
WHERE CA.Item_Id = 17 AND CA.field_id in (238, 326)

In the ON clause you were used the item_name and ATTR_VAL for matching. It returns the wrong result.

Upvotes: 1

Related Questions