sql query with multiple checkboxlist selected

Question

I have a complex query that includes some dynamic sql which partially depends upon a checkboxlist. Here's the part that has me stumped right now (brain fart?).

Simple example:

Table A (id, name)

Table B (id, Aid, Cid)

Table C (id, color)

So lets say Table A has:

1, Bob 2, Tim 3, Pete

and Table C has:

1, Red 2, Blue 3, Green

Now Table B has

1, 1, 1 2, 1, 2 3, 3, 2

So that Bob's favorite colors are Red and Blue and Pete's favorite colors are only Blue.

How do I query so that I only retrieve rows from Table A that have favorite colors of both Red and Blue. I don't want to see Pete in my resultset.

Answer 1

You want to use the INTERSECT operator to get those that match both, this is SQL 2005+ only, however.

SELECT name FROM TableA
WHERE ID IN (SELECT Aid FROM TableB WHERE CId = 1
             INTERSECT
             SELECT Aid FROM TableB WHERE CId = 2)

Answer 2

SELECT sr.receiving_id, sc.collection_id FROM stock_collection as sc, stock_requisation as srq, stock_receiving as sr WHERE (sc.stock_id = '" & strStockID & "' AND sc.datemm_issued = '" & strMM & "' AND sc.qty_issued >= 0 AND sc.collection_id = srq.requisition_id AND srq.active_status = 'Active') OR (sr.stock_id = '" & strStockID & "' AND sr.datemm_received = '" & strMM & "' AND sr.qty_received >= 0)