Jumat, 19 Februari 2016

RE: [MS_AccessPros] Join me in a discussion of .... joins

 

Thanks Liz,

 

I prefer the other way but I am wondering wheter there is a theoretical reason to prefer one or the other.

 

I have uploaded diagrams to the group's files

 

https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/Scenario%201.png

https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/Scenario%202.png

 

Glenn

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 9:55 AM
To: 'MS_Access_Professionals@yahoogroups.com' <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Join me in a discussion of .... joins

 

 

If I have a main table with subsidiaries, I prefer a star like join with the left linking out to the subsidiaries.

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 7:31 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Join me in a discussion of .... joins

Thanks John,



I have renamed my tables. The primary table is tblPeople. The other three are subclass tables, tblButchers, tblBakers, and tblCandleStickMakers. When I was setting up the demo, the query designer autojoined according to scenario 2. The query does not need to be updateable; it is simply for output purposes.



"Daisy Chain" (Scenario 1)







SELECT tblPeople.PersonLast, tblPeople.PersonMiddle, tblPeople.PersonFirst

FROM ((tblPeople LEFT JOIN qselButchers ON tblPeople.PersonID = qselButchers.PersonID)

LEFT JOIN qselBakers ON qselButchers.PersonID = qselBakers.PersonID)

LEFT JOIN qselCandleStickMakers ON qselBakers.PersonID = qselCandleStickMakers.PersonID;





"Three Joins" (Scenario 2)







SELECT tblPeople.PersonLast, tblPeople.PersonMiddle, tblPeople.PersonFirst

FROM ((tblPeople LEFT JOIN qselButchers ON tblPeople.PersonID = qselButchers.PersonID)

LEFT JOIN qselBakers ON tblPeople.PersonID = qselBakers.PersonID)

LEFT JOIN qselCandleStickMakers ON tblPeople.PersonID = qselCandleStickMakers.PersonID;



Glenn



From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 8:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Join me in a discussion of .... joins





Glenn-



I don't understand your second scenario. It would be clearer if you coded the FROM clause that you're using.



Note that if tables 2, 3, and 4 are all related to table 1 1-to-many, then a query including all four tables will not be updatable because you will be creating the Cartesian Product of all rows in 2, 3, and 4 that are related to 1.



John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)







On Feb 19, 2016, at 1:38 PM, 'Glenn Lloyd' argeedblu@gmail.com <mailto:argeedblu@gmail.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:



Bad pun most definitely intentional and offered without apology.



I would like to expand my knowledge of joins especially in the following scenario.



Table1 is the primary table and has say, 100 records.



Tables2, 3, and 4 are relate subsidiary tables with foreign keys related to Table1's primary key. Table 2 has 30 related records, Table3 has 10 and Table4 has 35.



As I see it, there are two different ways of setting up the joins with a number of variations that I won't introduce here.



Scenario 1:



Table1 - Left - Table2 – Left - Table3 – Left – Table4



Scenario 2:



Table1 – Left – Table2

Table1 – Left - Table3

Table1 – Left - Table4



From what I have observed, queries using either of these scenarios yield the same 100 records with null records where none of them have a foreign key to one of Table1's records.



My question is whether there are any significant differences, or hidden pitfalls to either of these approaches and which yields the more efficient query.





Glenn











[Non-text portions of this message have been removed]



------------------------------------
Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
------------------------------------


------------------------------------

Yahoo Groups Links





This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___

Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

Tidak ada komentar:

Posting Komentar