Many to one relationship

Hello

I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result...it's just ALL of the criteria must be matched and a row returned:

example:

Transaction table: id, reference

attribute table: attributeid, attribute

transactionAttribute: attributeid, transactionid

Example dat

Attribute table contains: 1 Red, 2 Blue, 3 Green

Transaction table contains: 1 one, 2 two, 3 three

transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)

If I pass in Red, Blue, Green - I need to be returned "one" only

If I pass in Red - I need to be returned "three" only

If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria

If anyone's able to help that would be wonderful!

Thanks, Paul

Hi Paul,

To get the result, you cannot pass the Attribute strings directly, you have to seperate them with commas. Each one is quoted with single quotes.

Here is an example for

SELECT * FROM TransactionAttribute
LEFT OUTER JOIN AttributeTable ON TransactionAttribute.AttributeId = AttributeTable.AttributeId
LEFT OUTER JOIN TransactionTable ON TransactionAttribute.TransactionId=TransactionTable.TransactionID
WHERE Attribute IN ('Red', 'Green')

HTH.

sql