Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Finding records in many-to-many table

    I have a many-to-many relationship table with the following:

    OrgID----------LangID
    5555-----------1
    5555-----------2
    6666-----------1
    6666-----------2
    7777-----------1

    I need to find the DISTINCT OrgID where LangID = 1 AND LangID = 2, therefore, I want to return '5555' and '6666' but not '7777' because there is only a record for LangID = 1 but NOT LangID = 2

    The LangID is passed in as a List where LangID = 1 or LangID = 1,2

    I have searched through all of my SQL references and haven't found anything on this. I can't use WHERE LANGID IN (1,2) because 'IN' is like 'OR', but what I want is 'AND'.

    Please help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select OrgID
    from manytomanytable
    group by OrgID
    having count(*) =
    (select count(distinct LangID)
    from manytomanytable
    where LangID
    in (1,2) -- customize this part
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    drop table test
    go
    create table test(OrgID int,LangID int)
    go
    insert test values(5555,1)
    insert test values(5555,2)
    insert test values(6666,1)
    insert test values(6666,2)
    insert test values(7777,1)
    go
    select distinct orgid
    from test t
    where exists(
    select 1 from test t1
    where t1.orgid=t.orgid and t1.langid=1
    and exists(select 1 from test t2
    where t2.orgid=t1.orgid and t2.langid=2))

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    None of these solutions worked for me. The first reply was close to the solution.

    I did find a solution.

    SELECT DISTINCT A.HIVORGID
    FROM HIVORG_LANG A
    WHERE LANGID IN (#FORM.LANG#) AND
    ( SELECT COUNT(HIVORGID)
    FROM HIVORG_LANG B
    WHERE B.HIVORGID = A.HIVORGID
    AND B.LANGID IN (#FORM.LANG#) ) = #LISTLEN(FORM.LANG)#

    I am doing this with ColdFusion, so my list of (1,2 = #form.lang#) is dynamically used in the query.

    Thanks to all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •