Combine Left and Right Join
Hello Experts,
I need some help with a query. My db design currently contains 3 levels of entities (following principle of grandparent, parent, child).
#1. "GrandParent" and "Parent" are linked via Junction Table
#2. "Parent" and "Grandchild" are linked via Junction Table
In most cases, I have "complete records" where all 3 levels are linked top to bottom. However, in some instances, I may only have records of "scenario" #1... while in other cases I only have those of #2.
To find the missing Grandparent, I use a Left join query... starting from the parent level.
To find the missing Grandchild, I use a Right join query... also starting the the parent level.
Not sure if it's possible, but I'd like to combine both Right and Left join query in a single query (vs. using two queries like right now).
I tried the UNION but that doesn't seem to work. Hopefully I provided enough info in order to get some general ideas as to how I can accomplish it.
Thanks,
EEH
I need some help with a query. My db design currently contains 3 levels of entities (following principle of grandparent, parent, child).
#1. "GrandParent" and "Parent" are linked via Junction Table
#2. "Parent" and "Grandchild" are linked via Junction Table
In most cases, I have "complete records" where all 3 levels are linked top to bottom. However, in some instances, I may only have records of "scenario" #1... while in other cases I only have those of #2.
To find the missing Grandparent, I use a Left join query... starting from the parent level.
To find the missing Grandchild, I use a Right join query... also starting the the parent level.
Not sure if it's possible, but I'd like to combine both Right and Left join query in a single query (vs. using two queries like right now).
I tried the UNION but that doesn't seem to work. Hopefully I provided enough info in order to get some general ideas as to how I can accomplish it.
Thanks,
EEH
To summarize the syntax, it would be something like
select field_names FROM (ParentTable LEFT JOIN GrandChildTable ON ParentTable.PK_Field = GrandChildTable.FK_Field) LEFT JOIN GrandParentTable ON ParentTable.FK_Field = GrandParentTable.PK_Field
I have just noticed that you use junction tables (assuming n to n relationships between tables?). So that would make it just slightly more complicated:
select field_names FROM (ParentTable LEFT JOIN (JunctionTableParentGrandC
LEFT JOIN (JunctionTableGrandParent INNER JOIN GrandParentTable ON JunctionTableGrandParent.G
select field_names FROM (ParentTable LEFT JOIN GrandChildTable ON ParentTable.PK_Field = GrandChildTable.FK_Field) LEFT JOIN GrandParentTable ON ParentTable.FK_Field = GrandParentTable.PK_Field
I have just noticed that you use junction tables (assuming n to n relationships between tables?). So that would make it just slightly more complicated:
select field_names FROM (ParentTable LEFT JOIN (JunctionTableParentGrandC
LEFT JOIN (JunctionTableGrandParent INNER JOIN GrandParentTable ON JunctionTableGrandParent.G
ASKER
Okay, I kinda expected that it might be challenging to come to a solution via just explaining it. So, I've uploaded a
sample db at (I forgot how to get to the EE upload site). If the one below doesn't work, please tell me how to get to EE uploads (thanks!).
http://www.bestsharing.com/files/ms001174543/Listbox%20v02.zip.html
If you feel comfortable downloading it, here are the steps to make sense of the problem:
1. Background Info:
===================
Again, I have 3 entity levels (grandparent, parent, child). The db's equivalent are:
a. PAR = grandparent
b. WL = parent
c. Issue = child
2. Opening up the Sample db:
==========================
The frmLogin is my default form when opening the db. I'm sure you know, but just in case... to get "behind the scene" please press/hold the SHIFT key.
3. Viewing the Db:
==================
a. now, the frmLogin has 2 listboxes: "Data Entry" and "Create Linkages"
b. first, if you were to look at each of the three data entry forms you'd find the following records counts:
-- "Create PAR": contains 4 records (PAR 1, PAR 2, PAR 3, PAR 4)
-- "Create WL": contains 6 recors (WL 1, WL 2, ..., WL 6)
-- "Create Issue": contains 9 records (Issue 1, Issue 2, ..., Issue 9)
c. then, the 2nd listbox create two forms to "make the connection" between >> Parent and Grandparent (WL to PAR) << as well as >> "Grandchild to Parent (Issue to WL)" <<
- More specifically, if you select on form "Link WL to PAR", you'll see the 4 PARs in the dropdown.
- also, you find all "available" (unassigned) WL in the left listbox. All WLs that are linked to a PAR is shown in the
right listbox.
- the same concept is applied for the 2nd form "Link Issue to WL". Except, in this case the WLs are in the drop-down
menu and the Issues are shown in the 2 listboxes.
Make sense so far??? I hope...
4. Now, viewing the report "View all Data"... THIS IS WHERE THE ACTUAL PROBLEM LIES...
==========================
- at first look, the report appears fine. It shows the 3-tier entities in top-down (left-right) view
- however, having a closer look (in conjunction with the 2 forms where I created the linkages), some records are not
shown. This is where the combination of joining the LEFT/RIGHT join will come handy.
- okay, what's the problem:
-- first, I suggest to print out the report "View All Data"
-- now, open form "Link WL to PAR"
-- by default, "PAR 1" is shown. It has one child record... "WL 2"... comparing it w/ my report, it looks fine
-- now, I select "PAR 2" from the drop-down menu... it has 2 children... "WL 1" and "WL 3"... again, comparing it to
the report, it appears fine.
-- finally, if I'd select "PAR 3" and/or "PAR 4"... I don't see any children. Okay, here's the first problem, my report doesn't show me "PAR 3" and "PAR 4". While these two grandparent-level records don't have any children (WLs), I still want to show them in the top portion of the report. So, that's where I'd apply the Right-Join in my query.
-- now, I'd do a similar review of the data shown in form "Link Issue to PAR". I won't go into details, but you get the idea... I'm simply comparing (with my report) which WLs have which children (Issues). I'm using a Right-Join to show the "---" (Issue level) for the "WL 3".
Okay, I may already have provided too much details for you to make sense of... hopefully you didn't mind and didn't cause any great confusion.
5. Here's what it bulls down to:
==========================
The report "View All Data" should show me all existing records... whether linked or not linked. Currently, it doesn't... for instance, "PAR 2" and "PAR 4" are missing.
How can I fix it?
Thanks,
EEH
sample db at (I forgot how to get to the EE upload site). If the one below doesn't work, please tell me how to get to EE uploads (thanks!).
http://www.bestsharing.com/files/ms001174543/Listbox%20v02.zip.html
If you feel comfortable downloading it, here are the steps to make sense of the problem:
1. Background Info:
===================
Again, I have 3 entity levels (grandparent, parent, child). The db's equivalent are:
a. PAR = grandparent
b. WL = parent
c. Issue = child
2. Opening up the Sample db:
==========================
The frmLogin is my default form when opening the db. I'm sure you know, but just in case... to get "behind the scene" please press/hold the SHIFT key.
3. Viewing the Db:
==================
a. now, the frmLogin has 2 listboxes: "Data Entry" and "Create Linkages"
b. first, if you were to look at each of the three data entry forms you'd find the following records counts:
-- "Create PAR": contains 4 records (PAR 1, PAR 2, PAR 3, PAR 4)
-- "Create WL": contains 6 recors (WL 1, WL 2, ..., WL 6)
-- "Create Issue": contains 9 records (Issue 1, Issue 2, ..., Issue 9)
c. then, the 2nd listbox create two forms to "make the connection" between >> Parent and Grandparent (WL to PAR) << as well as >> "Grandchild to Parent (Issue to WL)" <<
- More specifically, if you select on form "Link WL to PAR", you'll see the 4 PARs in the dropdown.
- also, you find all "available" (unassigned) WL in the left listbox. All WLs that are linked to a PAR is shown in the
right listbox.
- the same concept is applied for the 2nd form "Link Issue to WL". Except, in this case the WLs are in the drop-down
menu and the Issues are shown in the 2 listboxes.
Make sense so far??? I hope...
4. Now, viewing the report "View all Data"... THIS IS WHERE THE ACTUAL PROBLEM LIES...
==========================
- at first look, the report appears fine. It shows the 3-tier entities in top-down (left-right) view
- however, having a closer look (in conjunction with the 2 forms where I created the linkages), some records are not
shown. This is where the combination of joining the LEFT/RIGHT join will come handy.
- okay, what's the problem:
-- first, I suggest to print out the report "View All Data"
-- now, open form "Link WL to PAR"
-- by default, "PAR 1" is shown. It has one child record... "WL 2"... comparing it w/ my report, it looks fine
-- now, I select "PAR 2" from the drop-down menu... it has 2 children... "WL 1" and "WL 3"... again, comparing it to
the report, it appears fine.
-- finally, if I'd select "PAR 3" and/or "PAR 4"... I don't see any children. Okay, here's the first problem, my report doesn't show me "PAR 3" and "PAR 4". While these two grandparent-level records don't have any children (WLs), I still want to show them in the top portion of the report. So, that's where I'd apply the Right-Join in my query.
-- now, I'd do a similar review of the data shown in form "Link Issue to PAR". I won't go into details, but you get the idea... I'm simply comparing (with my report) which WLs have which children (Issues). I'm using a Right-Join to show the "---" (Issue level) for the "WL 3".
Okay, I may already have provided too much details for you to make sense of... hopefully you didn't mind and didn't cause any great confusion.
5. Here's what it bulls down to:
==========================
The report "View All Data" should show me all existing records... whether linked or not linked. Currently, it doesn't... for instance, "PAR 2" and "PAR 4" are missing.
How can I fix it?
Thanks,
EEH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
koutny,
that works absolutely PERFECT!!!
Thanks so much for that idea. I truly appreciate it.
EEH
that works absolutely PERFECT!!!
Thanks so much for that idea. I truly appreciate it.
EEH
Well, I am glad that I could have been of some help.
Thanks for the points!
Thanks for the points!
anyhow A LEFT JOIN B is the same as B RIGHT JOIN A