473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Joining Multiple Tables with "LIKE" Data

Brilstern
208 100+
Forgive me if this has been answered, but I am struggling with an inability to ask the right question.

Scenario:

I have 3 Tables, which I would like to create one Report with, so first I am beginning with a query to create the result set.

Tables:
Expand|Select|Wrap|Line Numbers
  1. TABLE A
  2. Identifier                  Text
  3. Control Number              Text PK
  4.  
  5. TABLE B
  6. Control Number              Text FK
  7. Control Enhancement Number  Text PK
  8.  
  9. TABLE C
  10. Control Identifier          Text FK
  11. CCI Number                  Text PK
Only Relevant fields are listed

Issue:
I want to Link TABLE C to both TABLE's A & B as well as Link B to A where a Control Number has a Control Enhancement Number

The data in TABLE C - Control Identifier matches values in both TABLE A Control Number, and TABLE B Control Enhancement Number.

The data in Table B - Control Number matches values in Table A Control Number.

To put it in plain text the Control Identifier can be linked to a Control Number or a Control Enhancement Number.

So a Control Number can have no Control Enhancement Number, no CCI Number, or neither, or both.

I have successfully linked it from CCI Number - to Control Enhancement and CCI Number to Control Number, and inner-joined those tables, but this forces me to only show Control Numbers and Control Enhancement Numbers where there is a CCI Number. Not all Control Numbers and Control Enhancement Numbers have a CCI Number. Therefore I am trying to link both Control Numbers and Control Enhancement Numbers to CCI Numbers.

A successful data set would look like the following:
Expand|Select|Wrap|Line Numbers
  1. tblA.[CN]  tblB.[CEN]  tblC.[CCIN]
  2. A-1                    6654321
  3. A-1        A-1(1)      4561321
  4. A-1        A-1(2)      6542112
  5. A-1        A-1(3)      
  6. A-2                    6546544
  7. A-2        A-2(1)      6546541
  8. A-2        A-2(1)      6549878
  9. A-3
  10. A-4        A-4(1)
  11. A-4        A-4(2)
Relationships:
Expand|Select|Wrap|Line Numbers
  1. tblA.[CN] ONE TO MANY tblB.[CEN]
  2. tblA.[CN] ONE TO MANY tblC.[CCIN]
  3. tblB.[CEN] ONE TO MANY tblC.[CCIN]
Where:
tblA.[CN] is TABLE A - Control Number
tblB.[CEN] is TABLE B - Control Enhancement Number
tblC.[CCIN] is TABLE C - CCI Number
Attached Files
File Type: zip SampleData.zip (287.0 KB, 58 views)
Jul 22 '16 #1

✓ answered by nico5038

So the final result should be a table with the outer join of control and optionally an enhancement:
A-1 {only control}
A-1 A-1(1) {control and enhancement}

joined with all tblCCI records:
A-1 CCI-654987 {control and CCI}
A-1 A-1(2) CCI-654654 {Control, enhancement, & CCI

For a UNION you need to have an equal number of rows, thus the [outer join] query will need a dummy field (select CN, CEN, Null as CCI from....)
Next the tblCCI needs to have an outer join with the tblCEN and have the same number of fields
so in pseudo code:
select CN, CEN, Null as CCI from OuterJoin CN and CEN
UNION
select CN, CEN, CCI from OuterJoin CCI

Nic;o)

11 1141
nico5038
3,080 Expert 2GB
The naming of the fields is a bit confusing, especially the fact that Control Identifier of tblC links to ControlNumber in Table A.
As far as I see, you would need to start with an outer join between tblA and tblB, giving the CN and CEN column (optionally filled).
Next you would need to link the tblC with tblA and/or tblB. I would probably add to the outer join query a field with the concatenation of tblA.Controlnumber and tblB.controlenhancementnumber (optional).
As far as I understand these two values (when both exist) will be equal.

So now you can create a UNION for tblC and the outer join based on a join by the concatenation of tblC ControlIdentifier and tblC ControlIdentifier to match the fully filled first columns and the second query for CCIN codes not matched in the first UNION query for the "single" value concatenation field.
Just make sure that this second query holds a "" dummy for the CEN column.
Finally add the outer join query with a dummy CCIN column to get the combinations not having a CCIN number.

I pseudo code:
1) First left join on tblA and tblB with a.controlnumber "dominant"
and filling field cn, cen and a concatenation of a and b controlnumber
2) UNION 3 fold:
a- select [cn], [cen], ccin from query 1 where tblC Control Identifier & Control Identifier matches the concatenation field.
gives x x x rows
UNION
b- select [cn], [cen], ccin from query 1 where tblC single [Control Identifier] matches the concatenation field. Exclude the tblC CCIN's already in previous (a-) query
gives x x rows
UNION
c select [cn], [cen], ccin from query 1 where no tblC CCIN has been found

Nic;o)
Jul 22 '16 #2
Brilstern
208 100+
I just got a quick read of your thoughts but I am headed out the door. I uploaded some sample data to help clarify what I am attempting to do.

To quickly explain more about the Table C - Control Identifier:

This value is linked to both Control Numbers (Table A) and Control Enhancement Numbers (Table B). The CCI is a data set that enhances Control Numbers but also enhances Control Enhancement Numbers. I hope this helps clear it up.

I will look at your solution later this evening. Thank you very much!!

-SB
Jul 23 '16 #3
Brilstern
208 100+
Ok so I see what you are saying but the data is not quite set up that way.

The values for each look as such:

tblA.[Control Number] {A-1,A-2,A-3,...}
tblB.[Control Enhancement Number] {A-2(1),A-2(2),A-3(1),A-5(1),...}
tblC.[CCI Identifier] {A-1,A-2,A-2(2),A-3(1),A-4,...}

So as you can see you can concatenation any of the three. You could concatenation tblA.[Control Number] & tblB.[Control Number].

So I have done this so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblA.[CN],tblB.[CEN]
  2. FROM tblA LEFT JOIN tblB on tblA.[CN] = tblB.[CN];
This of course gives the desired fields:

tblA.[CN] & tblB.[CEN] Optional

I am trying to play with the Union's but it isn't my strong suit. Thoughts?
Jul 23 '16 #4
nico5038
3,080 Expert 2GB
Hmm, looks to me that the [Control Text Indicator] holds the entire needed structure:
AC-1 (a) (1) means

AC-1 --- --- from Controls,
AC-1 --- (1) from Control Enhancements
AC-1 (a-z) --- set of tblCCI's values
Thus each letter gives a XXX XXX XXX row

While
AC-1 (1) means no split on CCI
thus a XXX XXX --- row
and
AC-1 (a) means no Enhancements, only CCI
thus a XXX --- XXX row
and
AC-1 means no CCI and no Enhancements
thus a XXX --- --- row


Is this the structure ?

Nic:o)
Jul 23 '16 #5
Brilstern
208 100+
Almost, it truly is a complex data set.

So in tblC.[Control Text Indicator] values are as such:

A-1 --- is a link directly to a control
A-1(a) --- is a link to sub-value "a" in the control text
A-1(a)(1) --- is a link to sub-value "a", micro-value "1" in the control text
A-1(1) --- is a link directly to a control enhancement
A-1(1)(a) --- is a link to sub-value "a" in the control enhancement text
So, in total, there are 5 possible combinations for the Control Text Indicator

Comparatively when looking at how the Key Fields can be are as such:

Expand|Select|Wrap|Line Numbers
  1. CN    CEN      CCI          Explanation
  2. A-1                         {only control}
  3. A-1   A-1(1)                {control and enhancement}
  4. A-1            CCI-654987   {control and CCI}
  5. A-1   A-1(2)   CCI-654654   {Control, enhancement, & CCI
So, in total, there are 4 possible combinations of the relationship
Jul 23 '16 #6
nico5038
3,080 Expert 2GB
So the final result should be a table with the outer join of control and optionally an enhancement:
A-1 {only control}
A-1 A-1(1) {control and enhancement}

joined with all tblCCI records:
A-1 CCI-654987 {control and CCI}
A-1 A-1(2) CCI-654654 {Control, enhancement, & CCI

For a UNION you need to have an equal number of rows, thus the [outer join] query will need a dummy field (select CN, CEN, Null as CCI from....)
Next the tblCCI needs to have an outer join with the tblCEN and have the same number of fields
so in pseudo code:
select CN, CEN, Null as CCI from OuterJoin CN and CEN
UNION
select CN, CEN, CCI from OuterJoin CCI

Nic;o)
Jul 24 '16 #7
zmbd
5,501 Expert Mod 4TB
+ Late to the game here; however, I'm trying to wrap my head around what you need...
You want all values from TableA
You want only the values in TableB that match the related field in TableA
You want values from TableC that have matching values in either TableA or TableB
Where "matching values" is between the Foreign Key and Primary Keys

Do I have that correct?


+ If possoble, one thing that you might consider when naming fields in tables:
PK_tablename for the field with your primary key
FK_tablename for the field with the foreign key where the tablename is the name of the related table

ie:
[TableA]
[TableA].[PK_TableA]

[TableB]
[TableB].[PK_TableB]
[TableB].[FK_TableA]

etc... one can always alias the field names in the query or change the text shown in the labels to be human friendly. Of course, that isn't always possible when dealing with outside or collaboration data projects.

Also, my personal preference is to place the [PK] as the first field in a table whenever possible.
Jul 24 '16 #8
Brilstern
208 100+
Thanks for the post zmbd. Long time no talk! Hope all is well. I think I might have most of the solution using some of Nico's theory, but I will dive into yours more tomorrow.

Nic;o),

So I got to thinking about it and what I really need to do is break it down as granular as possible so I wrote 4 query's to get the baseline data.

Query #1 -Only Controls that have no CE or CCI (This one I need help on)
Query #2 - Controls with only a CE
Query #3 - Controls with a CE and CII
Query #4 - Controls with on CII

So although I haven't figured out Query #1, here is a union for #2-#4 with all the correct data.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblControls.[Control Number], tblControlEnhancers.[Control Enhancement Number], tblCCI.[CCI Number]
  2. FROM (tblControls INNER JOIN tblControlEnhancers ON tblControls.[Control Number] = tblControlEnhancers.[Control Number]) LEFT JOIN tblCCI ON tblControlEnhancers.[Control Enhancement Number] = tblCCI.[Control Identifier]
  3. WHERE (((tblCCI.[CCI Number]) Is Null));
  4. UNION
  5. SELECT tblControlEnhancers.[Control Number], tblControlEnhancers.[Control Enhancement Number], tblCCI.[CCI Number]
  6. FROM tblControlEnhancers INNER JOIN tblCCI ON tblControlEnhancers.[Control Enhancement Number] = tblCCI.[Control Identifier];
  7. UNION
  8. SELECT tblControls.[Control Number], Null AS [Control Enhancement Number], tblCCI.[CCI Number]
  9. FROM tblControls INNER JOIN tblCCI ON tblControls.[Control Number] = tblCCI.[Control Identifier];
So my union query comes out right now with all Control Numbers with CE, CII, or both. I am just missing Controls with neither. The big thing here is a went from a left(outer) join to an inner join because I am adding all data separately.
Jul 25 '16 #9
Brilstern
208 100+
So, with how I am filtering my reports I am able to use the UNION query data set to correctly display all my data. By linking the Control & Control Enhancement Numbers in separate reports and then embedding the Control Enhancement Report I am able to get the desired format:

Control Number
____CCI Number (optional)
__Control Enhancement (optional)
____CCI Number (optional)

Thank you both very much for the input and helping me wrap my head around the data. Very much appreciate it!
Jul 25 '16 #10
nico5038
3,080 Expert 2GB
Great job Stevan, I really love it when a questionner solves a problem with only directions, as that's the best way to learn the trade and become a professional !

For me this was also fun to wrap my head around the structure !

Guess we'll see you soon as a contributer here at Bytes : - )

Nic;o)
Jul 27 '16 #11
Brilstern
208 100+
Thanks Nico, I have been around a while, I just tend to be active sometimes and other times not. Spent plenty of time talking to NeoPa and twinnyfo :)

If you are interested I am working on another issue with the same data. Trying to figure out the best way for filtering for a report.
Jul 27 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: MJD | last post by:
Hi, Having created a form in ms access using a single table as its data source, is there a way of adding a new text box whose data source comes from a different table in the database? I know it...
5
by: Sami | last post by:
I can create queries and reports based on info from one table. How do I create one using information from multiple tables. What do I need to make sure the information from one table will be...
0
by: dkintheuk | last post by:
Hi all, I'm trying to output a result set from some data as follows: I have raw data with the following columns Date, Country, System, ID,
1
by: Ahmet Karaca | last post by:
Hi. myds.Reset(); mycommand.SelectCommand.CommandText= "Select att1 from Ing as Ingredient, Pro as Product "+ "where Pro.ad='apple' and Pro.id=Ing.id"; mycommand.Fill(myds, "Product"); // Here...
3
by: yovation | last post by:
Hi, I have a 3 table database. 1 parent 1 child 1 child of child I would like to get the data into excel as 1 sheet (similar to a grouped report).
10
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they...
4
by: Cyprus106 | last post by:
Apparently, Im incapable of properly executing this query! I've been at this for far too long and gotten nowhere. Forgive me if this is a umb mistake; I'm not great at SQL. I've got three tables,...
2
by: Neekos | last post by:
Im working on web tool that will allow supervisors to see a list of their agents and their call stats for a call center. I have one main table that holds employee IDs and their supervisor names....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.