?
Solved

Getting records as fields (from attribute listing table)

Posted on 2007-08-10
7
Medium Priority
?
189 Views
Last Modified: 2010-03-20
Microsoft Access SQL Query
0
Comment
Question by:Microslave
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19670222
please explain your problem a bit more clearly.
let me guess that you want a rows-to-columns process, in which you want to search for Crosstab or pivot
0
 

Author Comment

by:Microslave
ID: 19670307
Lets say I have two tables, one table lists a bunch of car's, and the second lists a bunch of attributes that these cars can have.

[tblCarList]
carID  | carName
1        | Red Ford
2        | Blue Dodge
3        | Green GMC

[tblCarAttributes]
carID | attributeName | attributeValue
1       | EngineType     |  4 Cylinder
2       | EngineType     |  6 Cylinder
3       | EngineType     |  8 Cylinder
1       | TransmissionType |  Manual
2       | TransmissionType |  Auto
3       | TransmissionType |  SMG
1       | Spoiler Color   |  Orange
2       | Spoiler Color   |  Black

The problem is that each car does not have the same set of attributes (in the above example, carID=3 does not have 'Spoiler Color').  
My question is, how do I create a query to select the records in the attributes table as columns?

If they had the same set of attibutes, this query would work:
SELECT [000tblCarList].carNAme, engineTypeAtt.attributeValue AS EngineType, trannyTypeAtt.attributeValue AS TransmissionType
FROM (000tblCarList INNER JOIN 000tblCarAttributes AS engineTypeAtt ON [000tblCarList].carID = engineTypeAtt.carID) INNER JOIN 000tblCarAttributes AS trannyTypeAtt ON [000tblCarList].carID = trannyTypeAtt.carID
WHERE (((engineTypeAtt.attributeName)="Engine Type") AND ((trannyTypeAtt.attributeName)="TransmissionType"));

This returns all car records, with their Engine & Transmission Type. However once I put in the Spoiler Color field:
SELECT [000tblCarList].carNAme, engineTypeAtt.attributeValue AS EngineType, trannyTypeAtt.attributeValue AS TransmissionType, spoilercolorAtt.attributeValue AS SpoilerColor
FROM ((000tblCarList INNER JOIN 000tblCarAttributes AS engineTypeAtt ON [000tblCarList].carID = engineTypeAtt.carID) INNER JOIN 000tblCarAttributes AS trannyTypeAtt ON [000tblCarList].carID = trannyTypeAtt.carID) INNER JOIN 000tblCarAttributes AS spoilercolorAtt ON [000tblCarList].carID = spoilercolorAtt.carID
WHERE (((engineTypeAtt.attributeName)="Engine Type") AND ((trannyTypeAtt.attributeName)="TransmissionType") AND ((spoilercolorAtt.attributeName)="Spoiler Color"));

It does not return a record for the car that does not have a matching SpoilerColor record in the attribute table.

How do I go about doing this? On a side note, is there a way to set a fieldname to the value of a returned record? ie
... engineTypeAtt.attributeValue as engineTypeAtt.attributeName ...
?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19671371
>It does not return a record for the car that does not have a matching SpoilerColor record in the attribute table.
replace the INNER JOIN by LEFT JOIN, and it will return the records despite the missing records...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Microslave
ID: 19671705
Doesn't work. The join would work on carID, so there is a corresponding carID in both tables.

The record gets filtered out when adding the WHERE attributeName='Spoiler Color'.

It needs to be something like this, in pseduo:

Where attributeName='Spoiler Color'
Or carID does not exist in attributesTable where attributeName ='Spoiler Color'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 19672878
>The record gets filtered out when adding the WHERE attributeName='Spoiler Color'.
when doing LEFT JOIN, add the conditions to the JOIN condition, not to the WHERE condition:

SELECT [000tblCarList].carNAme, engineTypeAtt.attributeValue AS EngineType, trannyTypeAtt.attributeValue AS TransmissionType
FROM (000tblCarList LEFT JOIN 000tblCarAttributes AS engineTypeAtt ON [000tblCarList].carID = engineTypeAtt.carID AND  ((engineTypeAtt.attributeName)="Engine Type") )
LEFT JOIN 000tblCarAttributes AS trannyTypeAtt ON [000tblCarList].carID = trannyTypeAtt.carID AND ((trannyTypeAtt.attributeName)="TransmissionType"));
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20031583
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question