SQL criteria AND(this = that) AND(this = this) 1

[NSNewey]

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…

SELECT tPart.PartName, tPartDesc.Description, tPartDesc.Value
FROM tPartDesc RIGHT JOIN tPart ON tPartDesc.PartID = tPart.PartID

I tried to add this…

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???

 

[Remou]

I think you want:

WHERE (tPartDesc.Description = "Material" AND tPartDesc.Value = "Wood")
[red]OR[/red] (tPartDesc.Description = "Colour" AND tPartDesc.Value = "Blue")

 

[PHV]

One way:
SELECT P.PartName, M.Description, M.Value, C.Description, C.Value
FROM (tPart P
INNER JOIN tPartDesc M ON P.PartID = M.PartID)
INNER JOIN tPartDesc C ON P.PartID = C.PartID
WHERE M.Description = 'Material' AND M.Value = 'Wood'
AND C.Description = 'Colour' AND M.Value = 'Blue'

Another way:
SELECT P.PartName
FROM tPart P
INNER JOIN tPartDesc D ON P.PartID = D.PartID
WHERE (D.Description = 'Material' AND D.Value = 'Wood')
OR ( D.Description = 'Colour' AND D.Value = 'Blue')
GROUP BY P.PartName
HAVING Count(*) = 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NSNewey]

Thanks for the ultra fast responses...

Remou...
I tried that thanks but using OR means both Part1 and Part2 are returned as they both have descriptions that are either or.

PHV...
I will try both examples tomorrow (off home how)

Thanks both

 

[PHV]

Sorry for the typo:
SELECT P.PartName, M.Description, M.Value, C.Description, C.Value
FROM (tPart P
INNER JOIN tPartDesc M ON P.PartID = M.PartID)
INNER JOIN tPartDesc C ON P.PartID = C.PartID
WHERE M.Description = 'Material' AND M.Value = 'Wood'
AND C.Description = 'Colour' AND C.Value = 'Blue'

 

[NSNewey]

Hi PHV

Initially, I couldn't get either going but saw what you were up to and modified this fragment...
FROM (tPart P INNER JOIN tPartDesc

to

FROM (tPart [red]AS[/red] P INNER JOIN tPartDesc

and got what I needed.

Thanks very much.