How to combine these tabkles? (1 Viewer)

[ariansman]

Table1 has fields: A, B, C , D, E and I. Table 2 has fields: C, D, E, F, G, H. Fields C ,D and E are foreign keys.

Can we run a query to result Table3 with fields: A, B, C, D, F, G?
In other words we want a third Table with records combined of the first two tables. Obviously fields A and B will be blank in some of the rows, while F and G will be blank in other rows. There will be now row wherein all fields are filled.

I hope i provided enough information.
thank you

 

[sparks80]

Does this do what you want?

SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 INNER JOIN Table2 ON (Table1.C = Table2.C) AND (Table1.D = Table2.D)  AND (Table1.E = Table2.E)

 

[Pat Hartman]

You may need to do a full outer join. Since there is no integral way to do this, you would create a Union query that unions ALL a left join query with a right join query.

 

[ariansman]

Does this do what you want?

SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 INNER JOIN Table2 ON (Table1.C = Table2.C) AND (Table1.D = Table2.D)  AND (Table1.E = Table2.E)

I exactly copied and pasted the code in the SQL query. This was the result:

"type mismatch in expression"

 

[sparks80]

Hi,

The error you have described appears when you do not have the same data type on each side of the join. For example you can't join a number and text together.

I assumed given that fields C, D and E are foreign keys that they have one of these data types:
- autonumber
- number (long integer)

If one of your fields IS an autonumber, then the corresponding field must be set to number (long integer).

Check the defintion for your tables and make sure that the data type for fields C, D and E match.

 

[gemma-the-husky]

maybe rather than discuss fields as ABCDE, it would be better to explain exactly what the tables are.

generally, you need to join two tables on the fields common to both of them (note that this depends on the data being correctly normalised). you can then bring into the final query other fields from either of the two tables involved in this query.

 

[ariansman]

Hi,

The error you have described appears when you do not have the same data type on each side of the join. For example you can't join a number and text together.

I assumed given that fields C, D and E are foreign keys that they have one of these data types:
- autonumber
- number (long integer)

If one of your fields IS an autonumber, then the corresponding field must be set to number (long integer).

Check the defintion for your tables and make sure that the data type for fields C, D and E match.

Yes,
Thank you it worked.
However I still need help. I used this hypothetical problem to depict a simple picture of the main problem.
The mentioned code provides the columns i asked, but with no record. In fact, I want the rows from both tables to be put together. I was thinking to a union query to join the common fields, but keep the independent fields as well. It is possible? Shall i describe the real fields and tables?

 

[sparks80]

The solution I gave will only return records where C, D and E are all present and match. If only one set of records has been populated then they will not be included.

To return records where C, D and E have been filled out in one or other or both tables will require simulating a full outer join (as Pat described).

It is hard to write SQL code this complex in your head, but try giving this a go:

SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 INNER JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
UNION ALL
SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 LEFT JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
WHERE Table2.C Is Null
UNION ALL
SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 RIGHT JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
WHERE Table1.C Is Null

If this does not work then try posting some more information about the actual field names, the context may help to answer the question!

 

[ariansman]

The solution I gave will only return records where C, D and E are all present and match. If only one set of records has been populated then they will not be included.

To return records where C, D and E have been filled out in one or other or both tables will require simulating a full outer join (as Pat described).

It is hard to write SQL code this complex in your head, but try giving this a go:

SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 INNER JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
UNION ALL
SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 LEFT JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
WHERE Table2.C Is Null
UNION ALL
SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 RIGHT JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
WHERE Table1.C Is Null

If this does not work then try posting some more information about the actual field names, the context may help to answer the question!

Thank you,
Your guide was really constructive.
When I ran the above code I received the desired columns and records from table1, but the records from table2 are missing data for columns C and D, which are the common column between the two tables.
Could you please have a look at this, what I wrote based on your note?

SELECT Table1.A, Table1.B, Table1.C, Table1.D, Table2.F, Table2.G
FROM Table1 LEFT JOIN Table2 ON Table1.C = Table2.C AND Table1.D = Table2.D AND Table1.E = Table2.E
  UNION ALL 
SELECT Table1.A, Table1.B, Table2.C, Table2.D, Table2.F, Table2.G
  FROM Table1 RIGHT JOIN Table2 ON (Table1.E = Table2.E) AND (Table1.D = Table2.D) AND (Table1.C = Table2.C);

As you see, I omitted both the first query (inner join) and the Null criteria in your code. I did this omission because I did not understand them. The resultant query seems to have all records from both tables, in the rational accordance. do you approve what I wrote? Will the omissions cause any problem?

 

[sparks80]

Hi,

I have tried to explain the query by using a Venn diagram - see the attached image!

The query is made of three parts like this:

Part 1
Retrieve records where fields C, D and E exist and match in Table 1 and Table 2

Part 2
Retrieve records where fields C, D and E exist in Table 1, but not in Table 2. This rejects the records already in Part 1 by finding records where field C in Table 2 is null.

Part 3
Retrieve records where fields C, D and E exist in Table 2, but not in Table 1. Again this rejects the records already in Part 1 by finding records where field C in Table 1 is null.

All three tables combined together using Union All are the equivalent of performing a full outer join. I hope that makes sense, but let me know if it is still unclear.