I'm sure there is a much more efficient way of doing this, but this would work:
SELECT distinct ID from table where id in (select ID from table where val = 'A' and id in (select id from table where val = 'B' and ID in (select id from table where val = 'C'))) On Mon, Sep 26, 2011 at 1:18 PM, Richard White <rich...@j7is.co.uk> wrote: > > Hi, > > i know this is probably a simple answer and i probably drunk too much > coffee! > > given the following sql data: > > ID value > -------- > 1 A > 1 B > 1 C > 2 A > 2 B > 3 A > 3 B > 3 C > > i need to run a query that says return me the ids that are linked to values > A and B and C. > so this query on the above data would return IDs 1 and 3 > > thanks :) > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347716 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm