Thanks John,
Definitely food for thought. In my particular task at hand, I don't think the Cartesian product is an issue because the subsidiarity queries are actually crosstabs whose row source is the foreign key that relates back to the parent table. The purpose of the exercise is to flatten structured data for export to an analyst who can only use flat file data with his software.
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 11:08 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Join me in a discussion of .... joins
Glenn-
Jumping back in…
In Scenario 1, you'll get all the People and any Butchers who match on PersonID. If some Persons have no Butchers related, then you'll get Null values in the Butcher columns selected in the query. Then, you'll get any Bakers who match the PersonID of any Butchers who match a PersonID in People. If a Person exists who has a match in Bakers but not in Butchers, you won't get the Baker row because you're linking through Butchers. Finally, you'll get any Candle Stick Makers who match a PersonID found in Bakers that was also found in Butchers and People. Again, if there are Candle Stick Makers who match a PersonID in People but there's no matching row in both Butchers AND Bakers, you won't see the row.
In Scenario 2, you'll get all the People and any matching Butchers, Bakers, or Candle Stick Makers. BUT, if, for example, there are 2 Butchers that match, 3 Bakers that match, and 4 Candle Stick Makers that match, you will get 24 rows (2 x 3 x 4) for that person. That's the Cartesian Product I mentioned earlier.
Both techniques have downsides. In the first, you might miss some Bakers or Candle Stick Makers. In the second, you'll get multiple rows if there is more than one related row for a given person in the three sub-tables.
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
(Paris, France)
On Feb 19, 2016, at 4:51 PM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Glen, Yes. If I have a left join A to B and a left join B to C but there isn't a value in B that matches A then it doesn't join to C when in fact I would have wanted a value in C that had I left joined A to C I would get it. It is a more inclusive join for that purpose. If the data is such that there will always be a value in B so that C will link in and I would be using inner joins anyway, then I'm good.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 8:48 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Join me in a discussion of .... joins
Liz,
Does that result happen when the query is using left joins? I haven't noticed that effect with my data (but, of course, that concern is part of why I raised the question.)
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 10:03 AM
To: 'MS_Access_Professionals@yahoogroups.com' <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Join me in a discussion of .... joins
Glen, I've found that if one of my subsidiaries doesn't have a value or a link then it can break the link to another subsidiary. On the other hand, if I'm relying on the inner join for a data integrity issue, then I purposefully join in a subsidiary to a subsidiary.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, February 19, 2016 8:00 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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.
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.
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 (13) |
Tidak ada komentar:
Posting Komentar