If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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

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.