[RESOLVED] Need help with query to get common attributes from data in table

[sgarv]

Hi all, I have an application written in VB6, using DAO to access an MDB database (Access type). I need to generate a report and seem to be stuck. I hope some one can help or point me in the right direction.

The database is an inventory of objects. The main table holds the objects most basic data. In this case it is dealing with cars. There is a second table that for each car hold certain attributes.

The attributes are defined in a table called ObjAttributes and consists of an autonumber field called "Id" and a text description. Here is some sample data:

1 - Green
2 - Silver
3 - Red
4 - 4 wheel drive
5 - Power windows
6 - GPS
7 - 2 door
8 - 4 door
9 - Manual transmission

The main table consists of an automumber Id field, make and model of the car. The table is called InvItems. Very simple. :-) For example

1 - Chevy Malibu
2 - Ford Focus

The other table, called ObjDetails is a cross reference table that contains the InvItems.Id value and the ObjAttributes.Id value. The user can define 0 or multiple attributes for a given car. For example, taking the Ford Focus as an example, Id = 2, so the ObjDetails table can contain the following

1 - 2, 2
2 - 2, 6
3 - 2, 8

This describes the Malibu as being Silver (2), having a GPS (6) and 4 doors (8).

The query that I need should be able to find all cars given a list of common attributes. For example the query could request a list of cars that have GPS, are 4 door and are red. Or all cars that have power windows, are 4 wheel drive and have a manual transmission. Or just cars that are red and are 4 wheel drive.

I am having problems putting together a query that will work. I have something like this:

select * from ObjDetails where ObjAttributesId in (2,6,8)

In this case ObjAttributesId is the column name where the fk to ObjAttributes.Id is kept. This work for scenarios when I am looking for cars that have any of the listed attributes, but I also need one that gets all car that have all listed attributes. Thank you for you help and time. Best, Sgarv

[si_the_geek]

I think this will do what you want:

select InvItemsId, Count(ObjAttributesId)
from ObjDetails 
where ObjAttributesId in (2,6,8)
Group By InvItemsId
Having Count(ObjAttributesId) = 3

[sgarv]

Yes, that works perfectly! So simple! Much obliged! SGarv

I think this will do what you want:

select InvItemsId, Count(ObjAttributesId)
from ObjDetails 
where ObjAttributesId in (2,6,8)
Group By InvItemsId
Having Count(ObjAttributesId) = 3

[si_the_geek]

No problem.


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).

[sgarv]

Aaargh! I clicked the Thread tools option and the "Mark thread resolved" option did not appear! Searched about for clues... tried it again in case of brain system failure but got the same result. Finally it occurred to me to check whether I was logged in or not. I was not. Logged back in, retried Thread tools, found option to mark as resolved and marked it. I did not log out, so apparently the site logs off users automatically ... how helpful :-S. :-) Sgarv

[si_the_geek]

That was rather awkward, thanks for putting in all that effort

The site does log you out after 15 minutes, but if you tick "remember me" while logging in, it will automatically log you back in again next time you visit (as long as your cookies haven't been deleted).