Need some guiding ....

I have a simple table with three columns:

* Id(numeric)
* Reason for damage (i.e. Car accident)
* Damage code (i.e. V1009)

There are many Reason for damage for each Damage. I would like to get the Distinct Damage code where Reason for damage are i.e."Car accident", "Bicycle".

Any help is appriciatedsomething like :

SELECT
 DISTINCT [Damage code]
FROM YourTable
 WHERE ( [Reason for damage] = 'Car accident' OR [Reason for damage] = 'Bicycle' )

|||Thanks ndinakar,

The problem is that I need to get those [Damage code] that have both the specified [Reason for damage]. I have tried using AND instead of OR but i doesn't work.

Any good advice, anyone ...?|||In that case you can try:

SELECT
 [Damage code]
FROM
(
 SELECT DISTINCT
 [Damage code]
 FROM YourTable
 WHERE ( [Reason for damage] = 'Car accident')
 UNION ALL
 SELECT DISTINCT
 [Damage code]
 FROM YourTable
 WHERE ( [Reason for damage] = 'Bicycle' )
)
GROUP BY
 [Damage Code]
HAVING
 COUNT(*) > 1

Maybe someone else has something more elegant.

Terri|||Thanks tmorton

I get a errorcode complaining about the parantec, by the way does UNION ALL give the uniq [damage code ] that have both of the specified [Reason for damage]?

I only want those Damage code that fulfill the criteria of having the specified [reason for damage] ..|||Sorry, I forgot to use an alias in the FROM clause. It should be like this:

SELECT
 [Damage code]
FROM
(
 SELECT DISTINCT
 [Damage code]
 FROM YourTable
 WHERE ( [Reason for damage] = 'Car accident')
 UNION ALL
 SELECT DISTINCT
 [Damage code]
 FROM YourTable
 WHERE ( [Reason for damage] = 'Bicycle' )
) AS SQ
GROUP BY
 [Damage Code]
HAVING
 COUNT(*) > 1

This query will return all [Damage code]s that have both 'Car accident' and 'Bicycle' as [Reason for damage].

Terri|||Thanks Terri,

You saved my day !

Larra