SQL query to retrieve multiple items from a single column HELP.

[fuecheefang]

I need some suggestions:



What is the most efficient sql code to use, I have a column with Site location Mac codes about 3000 different items, and our department supportsabout 400 of these sites. What is the best query to use and can you all help supply an example?

SELECTMAC CodesFROMmy_tableWHEREMAC Codes IN('Value1','Value2',...);



Thanks.

[John_G]

Obviously putting 400 codes in a value list is a non-starter - it might work (it would be an awfully long string), but maintaining it would be a nightmare.

Is there any field in my_table that identifies which department supports that site? If so, could you use that in the Select statement?


For example:

SELECT [MAC Codes] FROM my_table WHERE Supporting_Department_ID = " & your_department_ID

If not, then do you have a table somewhere that lists the sites your department supports? If so, then you could have a SQL Select like this (call that table Site_List):

SELECT [MAC Codes] FROM my_table WHERE [Mac Code] in (Select [Mac Codes] from Site_List)

If you don't currently have such a table, creating one wouldn't be very difficult. You'd have to enter the list by hand I guess, but after that, maintenance is easy.