I need help to create my query.

Greetings! I really need help to create an SQL query cuz I really am having troubles figuring this one out.

Here are my tables:


tblItem tblContents tblProperties
=========== =========== =============
*pkIdItem *fkIdItem *pkIdProperty
sDescrItem *fkIdProperty sDescrProp
sValue


The * represents the primary keys.

The relations are as follow:
tblContents.fkIdItem is a foreign key of tblItem.pkIdItem
tblContents.fkIdProperty is a foreign key of tblProperties.pkIdProperty



Contents of tblItem:

pkIdItem sDescrItem
-------- ----------
1 Radio
2 Phone
3 Toolbox


Contents of tblProperties:

pkIdProperty sDescrProp
-------- ----------
1 Shape
2 Height
3 Weight


Contents of tblContents:

fkIdItem fkIdProperty sDescrProp
-------- ------------ ----------
1 1 Other
1 2 20
1 3 30
2 1 Round
2 2 20
2 3 45
3 1 Square
3 2 20
3 3 30



Now, what I want to do is to get a list of Item that have have a Height of 20 AND a weight of 30.

I know it really looks simple, but I just can't seems to figure out how to build the query!

Thank you for your help, it really is appreciated!
:D

Try this:


dim SQL as string

SQL = "SELECT DISTINCT tblItem.sDescrItem " & _
"FROM tblItem, tblProperties, tblContents " & _
"WHERE tblItem.pkIdItem = tblContents.fkIdItem " & _
"AND tblContents.fkIdProperty = tblProperties.pkIdProperty " & _
"AND tblProperties.sDescrProp = 'Height' "& _
"AND tblContents.sValue = 20 " & _
"UNION " & _
"SELECT DISTINCT tblItem.sDescrItem " & _
"FROM tblItem, tblProperties, tblContents " & _
"WHERE tblItem.pkIdItem = tblContents.fkIdItem " & _
"AND tblContents.fkIdProperty = tblProperties.pkIdProperty " & _
"AND tblProperties.sDescrProp = 'Weight' "& _
"AND tblContents.sValue = 30 "


- Eric

Well, the thing is that with the UNION combines the 2 results so, it gives me all the items that have a weight of 30 OR items that have a height of 20.

I really am looking for a way to get jsut items that have both properties...

But thanks anyway! I'll continue to try to find something...

:D

I believe you could either use a couple of IN clauses, or just reference your tblContents table twice:
SELECT pkIdItem, sDescrItem
FROM tblItem, tblContents Hgt, tblContents Wgt
WHERE pkIdItem = Hgt.fkIdItem AND Hgt.fkIdProperty = 2 AND Hgt.sValue = 20 AND
pkIdItem = Wgt.fkIdItem AND Wgt.fkIdProperty = 3 AND Wgt.sValue = 30

In clauses is an option I might use... but only as a last resorts! My tables have avout 200 000 items and 100 properties. And I think (please correct me if I am wrong) that using 100 IN clauses would dramatically decrease the speed for getting the results.

As the other piece of code you gave me well... THANK YOU!!! This works like a charm and is a little bit less ugly than the "IN" clause.

Thanks guys! This really gets me out of the jam!
:D

I agree, an IN clause would definitely be less efficient. Glad that helps. :)