UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Issues With Multiple Inner Join (many And Many To One), Access 2016    
 
   
Bryte_Lite
post Jun 11 2019, 09:39 PM
Post#1



Posts: 9
Joined: 11-June 19



Hi Everyone,

I'm new to these forums although I have been lurking for some time now. On a side topic, this forum really is amazing on how much Access knowledge there is here.
Anyhow, I've been trying to wrap my head around the idea of joining to a record multiple times. This is usually pretty standard from what I understand with normal SQL, but it seems like MSSQL or whatever MS Access uses has a specific process for using this.

I'm trying to repeat a join using the same two tables onto two separate columns.

I've attached the two tables I'm using in the attachments as .png files.

Here's the code I'm trying to make everything join:

CODE
SELECT  clothing_order.ID, A.color1, B.color2
FROM clothing_order INNER JOIN (SELECT * FROM clothing_order INNER JOIN color ON (clothing_order.color2 = color.ID) AS A) ON clothing_order.color1 = color.ID AS B;


If anyone could give some advice I would appreciate it.
The point of this exercise for me is to figure out how Access handles multiple joins for normalized tables.

Bonus Points: if you can include other field piece_of_clothing in the mix that would be great too. That one uses a different table similar to the color table.
Attached File(s)
Attached File  Untitled.png ( 4.27K )Number of downloads: 1
Attached File  Untitled1.png ( 2.97K )Number of downloads: 2
 
Go to the top of the page
 
MadPiet
post Jun 11 2019, 09:46 PM
Post#2



Posts: 3,131
Joined: 27-February 09



You can join on more than one column if you want... Say Table A and Table B have col1 and col2 and the types match.

SELECT A.field1A, A.field2A, B.Field1B, B.Field2B
FROM A INNER JOIN B ON (A.Field1A = B.Field1B AND A.Field2A=B.Field2B)
….

Go to the top of the page
 
Bryte_Lite
post Jun 11 2019, 09:59 PM
Post#3



Posts: 9
Joined: 11-June 19



QUOTE
You can join on more than one column if you want... Say Table A and Table B have col1 and col2 and the types match.

SELECT A.field1A, A.field2A, B.Field1B, B.Field2B
FROM A INNER JOIN B ON (A.Field1A = B.Field1B AND A.Field2A=B.Field2B)
….


Syntactically this works, but I end up getting empty returns, is there a trick to the SELECT ?

Also,
QUOTE
FROM A INNER JOIN B ON (A.Field1A = B.Field1B AND A.Field2A=B.Field2B)
A and B are aliases for the same table, "color". I'm trying to achieve clothing_order.color1 = color.ID AND clothing_order.color2 = color.ID but having the text return from color table.

In the case of clothing_order = 1 ...

clothing_order.color1 = color.ID = 4
clothing_order.color2 = color.ID = 5

So I should get returned {1, yellow, orange}.
This post has been edited by Bryte_Lite: Jun 11 2019, 10:12 PM
Go to the top of the page
 
GroverParkGeorge
post Jun 12 2019, 07:25 AM
Post#4


UA Admin
Posts: 35,141
Joined: 20-June 02
From: Newcastle, WA


Part of the problem is an inappropriate table design to begin with. That is the reason you have to engage in more difficult SQL syntax to use the "repeating columns".

You have multiple fields called "colorX", i.e. "Color1", "Color2" and possibly more?

This should be resolved into a couple of tables, one the parent and the other a child table to that parent. With a proper table design, the query becomes simpler and easier to implement.

I think the reason you probably don't get results is that some "colorX" fields are not entered, leaving Nulls in those fields in those records, and that means your query won't return correct results.

I think you can eventually cobble together a work-around, but the better resolution will be to correct the table design, as Roger described in that series of blog posts.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Bryte_Lite
post Jun 12 2019, 02:27 PM
Post#5



Posts: 9
Joined: 11-June 19



QUOTE
Part of the problem is an inappropriate table design to begin with


I don't think it is inappropriate, perhaps not normal but I can't see why it is inappropriate. In this case, there is a possibility for values to repeat in the table, but I don't think this is a common scenario. Fields in all tables are correctly populated.

QUOTE
This should be resolved into a couple of tables, one the parent and the other a child table to that parent


If I make another parent and child, I could lose data associated with both values together. If it were to be broken down further, and it is important to have the data on table (perhaps aliased) would you switch to fixed attributes?

You mentioned a work around?
Go to the top of the page
 
MadPiet
post Jun 12 2019, 09:11 PM
Post#6



Posts: 3,131
Joined: 27-February 09



How did I miss that??? Yeah. Your table design needs work. If you don't have confidential data in it, you could post the database and I might be able to fix it. (Well, if I can't shame on me... I've been doing this long enough!)

I had to deal with repeating columns [censored] a long time ago. Save yourself some serious headache and fix it before you go further. That "no repeating groups" rule is a big deal. Don't worry about forms and reports yet. I would focus on your design and make sure your table design is right. it will make your life SO much easier later on it's just not funny.


This post has been edited by MadPiet: Jun 12 2019, 09:25 PM
Go to the top of the page
 
Bryte_Lite
post Jun 13 2019, 07:03 AM
Post#7



Posts: 9
Joined: 11-June 19



The tables that need work are in my original post as attachments. The idea is that the shirt can have more than one color in its design. So more than one color-id is stored to account for multiple different colors.
Go to the top of the page
 
GroverParkGeorge
post Jun 13 2019, 07:55 AM
Post#8


UA Admin
Posts: 35,141
Joined: 20-June 02
From: Newcastle, WA


Okay, let's step back.

A properly designed table does NOT have multiple fields called "Attribute1', "Attribute2', etc.

That is what you have to do in a spreadsheet. It is not appropriate in a relational database table.

Please read the blog articles to which I previously linked. Read all of them, please.

Then compare, "Color1", "Color2" etc. to the columns called out in Roger's discussion.

"If I make another parent and child, I could lose data associated with both values together." How would that happen?

You insert one RECORD per color per clothing item. So if a shirt has two colors in it, the ItemAttributes table will have two records for that shirt, one for each color.

And that brings up another consideration. Do you only produce shirts? If so, you can have a Shirt table. If not, it will be an "Item" table with one attribute for ClothingType, e.g. "Shirt", "Pants", "Sweater", and so on.

And still another. How about materials? Are all clothing items made of one and only one type of fabric? Or could a clothing item combine two or more types of fabric? Cotton body with silk color, for example (I know, a stretch but only to illustrate a possible consideration).

Once your data model is set up appropriately, queries of this sort become much easier.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Bryte_Lite
post Jun 13 2019, 07:59 AM
Post#9



Posts: 9
Joined: 11-June 19



UPDATE:

Found a solution on stackoverflow that seems to be working:

CODE
SELECT t.PhoneNumber1, t.PhoneNumber2, t.PhoneNumber3
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2, t3.someOtherFieldForPhone3
FROM
(
(
  Table1 AS t LEFT JOIN Table2 AS t3 ON t.PhoneNumber3 = t3.PhoneNumber
)
LEFT JOIN Table2 AS t2 ON t.PhoneNumber2 = t2.PhoneNumber
)
LEFT JOIN Table2 AS t1 ON t.PhoneNumber1 = t1.PhoneNumber;


I'll keep this thread open for ideas to make tables better. 😁
Go to the top of the page
 
Bryte_Lite
post Jun 13 2019, 08:30 AM
Post#10



Posts: 9
Joined: 11-June 19



QUOTE
You insert one RECORD per color per clothing item. So if a shirt has two colors in it, the ItemAttributes table will have two records for that shirt, one for each color.


This makes sense, in a way, focusing on the color creates the need for it to come first in the hierarchy. It doesn't look very straightforward in this sense, but I can see your point.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 09:53 AM