JOINing two tables - need method 1

[moltar]

Thanks in advance for any assistance that can be provided...

I have two tables that I would like to join in a query.

Table #1
Attributes ATT
.AttributeID
.AttributeName

Table #2
CategoryAttributes CA
.CategoryAttributeID (the index)
.AttributeID
.CategoryID

There are about 20 records in the Attributes table, and I am trying to get a query that will return all 20 Attributes records, with another column that would have any value only if the Attributes.AttributeID is found in the CategeoryAttributes.AttributeID if CategoryAttributes.CategoryID=71, where 71 is just a particular CategoryID. If an AttributeID is not found in the CA table where CA.CategoryID=71, then I want a null value or the like to be returned.

For examples sake, the CA table has 5 records where CategoryID=71. So far, every query I try results in a returned set of just the 5 attributes found in the ATT table. What I really want is something returned like this:

ATT.AttributeID | ATT.AttributeLabel | CA.[any one field]
------------------------------------------------------
1 Par [null]
2 Slope [a value]
3 Holes [null]
etc.

In this example, the CA table would have the CategoryID of 71 with an AttributeID of 2, and CA would have no listing for a CategoryID of 71 with an AttributeID of 1.

Any ideas? Thanks!

 

[Pattycake245]

Hope this is what you're after:

select a.attributeid, a.attributename attributelabel, b.categoryid
from attributes a left join categoryattributes b on a.attributeid=b.attributeid
order by a.attributeid

Tim

 

[moltar]

thanks, Tim, but unfortunately that query does not work.

it returns a recordset that has a row for every categoryID found in the CategoryAttributes table (b in your example). what is needed is to just have one row for every attributeID found in the Attributes table (a in your example), with an additional column that has a value only if that attributeID is found in the CA table for a particular CategoryID value. if that attributeID is not found in the CA table where the CA.CategoryID=71 (for example), then there should be a null value or the like.

so if there are 20 records in the A table, the resulting joined set should have 20 records, too. there just needs to be another column that has a value or not, based on if that row's A.AttributeID is found in CA where CA.CategoryID=71.

Any ideas? Thanks!

 

[Pattycake245]

Sorry, I missed that part of your question.

This is one way to do it.

select a.attributeid, a.attributename attributelabel, case when b.categoryid=71 then b.[any column] else null end [any column]
from attributes a left join categoryattributes b on a.attributeid=b.attributeid
order by a.attributeid

This should work for you

 

[moltar]

Thanks! That got me to where I needed to be!