Many-to Many Join

[jennifer]

I am trying to do a many-to-many join on Id's from two separate files. I
like all Id's from both files to show in the query regardless of whether they
have a matching ID from either file. I believe that I have the query running
now, but I am needing the ID's to line up on the same line. For example,
right now ID#11 from one file is showing up on say line 4 and and because
there are more ID's in the second file ID#11 is showing up on line 5. Is
there anyway to fix this? Thanks

 

[Douglas J. Steele]

It's not clear what you're trying to do.

"a many-to-many join" has a very specific meaning in database design. The
implication is that you've got two tables, and that one entry in Table1 may
be related to one or more entries in Table2, and that one entry in Table2
may be related to one or more entries in Table1 so that you need to
introduce a third table to handle that relationship. Look in the Northwind
database that comes with Access. There's a many-to-many relationship between
Orders and Products (one order can contain many products, one product can be
contained on many orders), so table Order Details was introduced.

Are you simply trying to produce a list of both tables, along the lines of:

Table1 Table2
1 1
2
3
4 4

In the above, Table1 contains values 1, 2 and 4, while Table2 contains
values 1, 3 and 4.

To get a listing like that, you need to use a Join query:

SELECT Table1.ID, Table2.ID
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
UNION
SELECT Table1.ID, Table2.ID
FROM Table2 LEFT JOIN Table1
ON Table1.ID = Table2.ID

 

[jennifer]

Ok Thanks! I have a follow up question. If I would like to add the amounts
in for the individual ID's, where would I place that in the SQL statement?
For example.

ID Amount Owed ID Amount Paid
1 100
2 200
3 200 3 200

and so on.

Thank you so much for your help!

 

[Douglas J. Steele]

SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount Owed]
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount Owed]
FROM Table2 LEFT JOIN Table1
ON Table1.ID = Table2.ID


Incidentally, if you're trying to compare the two tables, you might find the
following easier:

SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount Owed],
"DifferentAmounts" AS Comment
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table1.[Amount Owed] <> Table2.]Amount Owed]
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount Owed],
"Not in Table2"
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount Owed],
"Not in Table1"
FROM Table2 LEFT JOIN Table1
ON Table1.ID = Table2.ID
WHERE Table1.ID IS NULL
ORDER BY 5, 1, 3

 

[Clif McIrvin]

Jennifer, if you're finding working directly with SQL a bit daunting
here's a union query I did based on a set of queries (sort of like
Doug's second example below) except that I built each query separately
and saved it. That way I could build (and test) each query in the
familiar design grid. Here's the code for the union to give you an idea:

select * from [xs Cylinder Lots Query new1] union all
select * from [xs Cylinder Lots Query new2] union all
select * from [xs Cylinder Lots Query new3] union all
select * from [xs Cylinder Lots Query new4] UNION ALL select * from [xs
Cylinder Lots Query new5]
ORDER BY [Test Date];

the ORDER BY clause gets it's field names from the first query; for a
union to work each query has to have the same number of fields (It helps
if you design them so the fields (columns) are in the same order.)

--
Clif

SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount
Owed]
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount
Owed]
FROM Table2 LEFT JOIN Table1
ON Table1.ID = Table2.ID


Incidentally, if you're trying to compare the two tables, you might
find the following easier:

SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount
Owed], "DifferentAmounts" AS Comment
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table1.[Amount Owed] <> Table2.]Amount Owed]
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount
Owed], "Not in Table2"
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT Table1.ID, Table1.[Amount Owed], Table2.ID, Table2.[Amount
Owed], "Not in Table1"
FROM Table2 LEFT JOIN Table1
ON Table1.ID = Table2.ID
WHERE Table1.ID IS NULL
ORDER BY 5, 1, 3


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok Thanks! I have a follow up question. If I would like to add the
amounts
in for the individual ID's, where would I place that in the SQL
statement?
For example.

ID Amount Owed ID Amount Paid
1 100
2 200
3 200 3 200

and so on.

Thank you so much for your help!