How to use multle values for Where clause

Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.

I have the following tables

tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing

bid_id Uci_id

123 1000

123 2000

123 1050

tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.

Uci_ID Sub_ID

1000 456

1000 2345

2000 456

1050 2345

2000 2345

This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.

Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.

This is how you have to do it

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)

|||Try this... SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID in (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)|||

There are 2 ways.

SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123

Hope this will help.|||

Thanks.

By Changing the = to IN, it worked perfectly.