Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    1

    Unanswered: Fan Trap or Multiple one-to-many joins

    I'm having a problem creating a view on some data that involves two one-to-many joins like this:

    tbl1 m----> tbl2 <----n tbl3

    and I need to create a view on the data from all three tables without duplicates from tbl1 and tbl3.

    The problem is that tbl1 and tbl3 are not related at all, except that they are linked by data in tbl2.

    Think of it like this: you have a project, which can have multiple consultants, and multiple stakeholders, and the data must be returned in such a way that each consultant and each stakeholder appears once in the output (the project name must appear multiple times of course). The issue is that the following two datasets are logically distinct but semantically identical:

    proj A, consultant A, stakeholder A
    proj A, consultant B, stakeholder B
    --
    proj A, consultant B, stakeholder A
    proj A, consultant A, stakeholder B

    but what I'm aiming for is:

    proj A, consultant A, stakeholder A
    proj A, consultant B, stakeholder B

    I've heard this described as a fan trap, but usual solutions involve reorganzing the data so that tbl3 joins tbl1 joins tbl2, but in my case there is no link there

    Incidently, the intended platform for this is DB2 and/or SQL Server. Any help appreciated, thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your examples are not very clear

    you start out by diagramming tbl1, tbl2, tbl3, and then immediately switch to projects, consultants, and shareholders, without showing the actual data in these tables, just some apparent cross join query results

    you might wish to show a more comprehensive example, because so far, it's hard to understand what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've heard this described as many things, most of which aren't polite to repeat.

    You're trying to figure out how to build a join to show the relationship between consultants and shareholders, to produce a one-to-one join between two tables that explicitly have no relationship. If you figure out how to make this happen, please let me know... I'm sure that I'll be fascinated by the explanation!

    You've got a clear relationship between project and shareholder, and another relationship between project and consultant. You don't explicitly state that there must be a shareholder or a consultant for any given project, and at some point in the project's life I can guarantee that there will not be one of either. You don't explicitly state that there must be one shareholder for every consultant. If you think about these requirements, unless at least one of these requirements is false, you can't get the output you want... There ain't no way to git there from here.

    You need to rethink either the specifications or the requirements. Something has got to give because using the definitions that you've given, the present problem can't be solved.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, so that's what that is -- i had never heard that terminology before

    this pdf is a pretty good explanation --
    http://support.businessobjects.com/d...sign/ut001.pdf

    i would solve this problem with a UNION query --

    proj A, consultant A
    proj A, consultant B
    proj A, stakeholder A
    proj A, stakeholder B
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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