Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: "One to Many" Query

    Hi,
    dont know how i got stumped on this...maybe easy for you guys..
    Table that has a layout like this:

    FileNumber------Agents
    001-------------Barney
    001-------------Joey
    001-------------Jennifer
    002-------------Bart
    003-------------Joey
    003-------------Barney

    i want to create a query that will only show records which have Barney AND Joey in the same File (or the same FileNumber). I also want to be able to extend this to more than 2 agents.

    in this case, it should produce 001, and 003

    thankx

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    If you want people to type in names then multiple MsgBox entries, or if you want to select from a list, a multi-select list box. Other possibilities?
    w

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    for the mean time, just those two guys.
    is there a sql string that can do that job?
    thats wat i'm asking.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Later: WRONG!

    might not be optimal, but does this work?
    select * from yourTable where ((agent ="joey") and (agent in (select agent from yourTable where agent = "barney")));


    izy
    Last edited by izyrider; 10-12-04 at 14:26.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    revised:

    select * from yourTable where ((agent = "joey") and (filenumber in (select filenumber from yourTable where agent = "barney")));

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    yea that does work,..but for the sake of simplicity, i had said one table.
    actually there are three tables, and inner and outer joins have to done to get the fileNumber and agent info. Since having a cut and dry sql statement will not allow for versatility (e.g, the user wants to include an extra agent restriction (+ AND Agent="extraperson")...), i made a base query that produces the filenumber and the agent, and made a second query which pulls in this first query, and for multiple agent restrictions, you pull in the first query n times for n agent restrictions. It works beautifully...thankx for the concept that made it work!

Posting Permissions

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