Hi,
Can someone help me with some SQL
I have a form with a listbox showing parts in a database.
I have several combo boxes allowing the user to select various criteria to drill down to the part he wants.
I have two tables (tPart and tPartDesc)
tPart holds details of a part in the database
tPartDesc has a one to many relationship with tPart so that the user can store as many part descriptions for a part as is required.
tPartDesc has three fields, PartID, Description and Value
I am trying to write a query which returns any part containing up to five description/value pairs which the user selects in a form.
An example of the query without the WHERE clause…
Part Number Description Value
Part 1 Material Wood
Part 1 Colour Red
Part 1 Shape Square
Part 2 Material Wood
Part 2 Colour Blue
Part 2 Shape Square
Lets say I want to return any parts with related descriptions that have the values Material=Wood and Colour=Blue.
Looking at the example records above, I want to see only Part 2 returned as it’s the only part with material = wood AND colour = blue
The SQL that returns the records above without the WHERE clause is…
I tried to add this…
…which returns nothing.
I can see why this returns nothing because I’m asking for the description field to contain “Material” and “Blue” on a single record which of course it won’t.
I hope you can understand what I am trying to achieve.
Is there a SQL guru out there who can point me in the right direction???
Can someone help me with some SQL
I have a form with a listbox showing parts in a database.
I have several combo boxes allowing the user to select various criteria to drill down to the part he wants.
I have two tables (tPart and tPartDesc)
tPart holds details of a part in the database
tPartDesc has a one to many relationship with tPart so that the user can store as many part descriptions for a part as is required.
tPartDesc has three fields, PartID, Description and Value
I am trying to write a query which returns any part containing up to five description/value pairs which the user selects in a form.
An example of the query without the WHERE clause…
Part Number Description Value
Part 1 Material Wood
Part 1 Colour Red
Part 1 Shape Square
Part 2 Material Wood
Part 2 Colour Blue
Part 2 Shape Square
Lets say I want to return any parts with related descriptions that have the values Material=Wood and Colour=Blue.
Looking at the example records above, I want to see only Part 2 returned as it’s the only part with material = wood AND colour = blue
The SQL that returns the records above without the WHERE clause is…
Code:
SELECT tPart.PartName, tPartDesc.Description, tPartDesc.Value
FROM tPartDesc RIGHT JOIN tPart ON tPartDesc.PartID = tPart.PartID
I tried to add this…
Code:
WHERE (tPartDesc.Description = “Material” AND tPartDesc.Value = “Wood”)
AND (tPartDesc.Description = “Colour” AND tPartDesc.Value = “Blue”)
…which returns nothing.
I can see why this returns nothing because I’m asking for the description field to contain “Material” and “Blue” on a single record which of course it won’t.
I hope you can understand what I am trying to achieve.
Is there a SQL guru out there who can point me in the right direction???