Join 4 Tables

[PicoTTS]

Hi,

I am having trouble joining 4 tables. I have 3 tables that are databases for different types of products and the fourth table houses all of the order details (customer name, address, etc). All 4 tables are linked by the Order ID

My tables are generally formatted as follows.



Table 1
OrderID, Description

Table 2
OrderID, Description

Table 3
OrderID, Description

Table4
OrderID, Customer, Address

My goal is to have a Union that lists all lines from table 1-3, and for each line, append that line with the corresponding Order info from table 4 based on the matching OrderID. I understand that I need to union all for first 3 tables but I am not sure how to join the fourth in the manner that I want. My guess is that I need to do an inner join but I have not been able to figure it out yet.

Thanks

[pbaldy]

Well, one would question why the 3 tables, but you'd join table 4 against each of the others separately. In other words, each of the 3 SQL statements in your UNION query would have a join.

[PicoTTS]

I had to keep 3 separate for each product type because they all have very different fields that define the characteristics of the product. In reality, the Description field I mentioned earlier is a concatenate of all the various characteristics.

I currently came up with something like this: But it is not showing any of the info from table 4. It is only showing the OrderID and the Description Column.

Select [Table1].OrderID, [Table1].DESCRIPTION from [Table1]
Inner Join [Table4] On [Table1].OrderID=[Table4].[OrderID_Number]
UNION ALL
Select [Table2].OrderID, [Table2].DESCRIPTION from [Table2]
Inner Join [Table4] On [Table2].OrderID=[Table4].[OrderID_Number]
UNION ALL
Select [Table3].OrderID, [Table3].DESCRIPTION from [Table3]
Inner Join [Table4] On [Table3].OrderID=[Table4].[OrderID_Number]

[pbaldy]

You'd have to add the field from table 4 to each of the SELECT clauses:

Select [Table1].OrderID, [Table1].DESCRIPTION, Table4.Whatever from [Table1]

[PicoTTS]

Perfect! This was exactly what I needed

[pbaldy]

Happy to help!