Re: Many-to-many self-relationships

On Fri, 8 Jul 2005 at 17:12:47, Stefan Rybacki <stefan.rybacki@xxxxxxx> wrote:
Rick Bowlby wrote:
<snip>
1) A table of Links including 2 columns "Person1" and "Person2", as well as the other Link attributes. This would have the disadvantage of needing a union of two queries whenever you needed to retrieve all the links connected to a particular Person. It just feels wrong.
<snip>
Its called m:n realation. And the first variant you presented is IMHO the correct one. Since it is exactly what a normalisation would do to one table that hold all information.

What do you mean by you need an union of two queries whenever you need to retrieve all links to a person?

What about a this example:
SELECT * FROM person JOIN link ON (person.person_id=link.person1 OR person.person_id=link.person2) WHERE person.person_id=xxx

This is one query and gives you all linked contacts to a specific person.

<snip> 

Regards
Stefan


Thanks for the response Stefan.

What I had meant by needing two queries, was something like:
SELECT * FROM person WHERE person_id IN (
SELECT person1 FROM link WHERE person2=XXX
UNION
SELECT person2 FROM link WHERE person1=XXX
)
Your SQL is much better.

Rick
--
Rick Bowlby

Use Reply-To for reply

.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... Using my query names, can you say exactly what my SQL should be ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)
  • Re: White Paper on How MSAccess interacts with SQL Server Back End?
    ... this newsgroup is about ADP and has nothing to do with ODBC linked ... For simple queries, Access will create a TSQL queries that will run on the ... these keys to retrieve the other fields from the tables by group of 10 rows. ... access path, hands it off os SQL2K, how SQL interprets it and then ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Question about Queries with Subqueries
    ... performance reasons to use UNION ALL instead of UNION. ... i have a query i am working on that draws data from 3 different data ... i have a question about queries like this. ... to try "flattening" my queries by using subqueries. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: union SQL programming
    ... only one of the 3 queries at a time, or else store the results of the union ... query in a temporary table, then do all sorts of complicated manipulations ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
Loading