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.

Matt Whitfield · Accepted Answer · 2010-02-08 16:22:36Z

2

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)

answered Feb 8, 2010 at 16:22

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nekoian Over a year ago

Luckily I'm using SQL Server 2005. Thanks Matt!

wowcat · Accepted Answer · 2010-02-08 16:24:37Z

0

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)

answered Feb 8, 2010 at 16:24

wowcat

1112 bronze badges

Collectives™ on Stack Overflow

sql query with multiple checkboxlist selected

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related