Combining Multiple Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining Multiple Tables

Author

Topic

quanom
Starting Member

4 Posts

Posted - 2011-12-12 : 09:12:08

Hi all,

I'm new to SQL and I want to ask you a question about combining multiple tables.

I have 5 tables and I want to combine them into 1 table.

I'm connecting to the database server through ODBC. and I'm using MS Access.

Here are my tables:

First table columns:

Posting Date, Document No, Description, Amount

Second table columns:

Vendor No, Description, Document Date

Third table columns:

Customer No, Description, Document Date

Fourth table columns:

Vendor No, Name, Vat Office, Vat No

Fifth table columns:

Customer No, Name, Vat Office, Vat No

With the aggregation of these five tables, I want a table with these columns:

Posting Date, Document Date, Document No, Description, Name, Vat Office, Vat No, Amount

In other words, I want to add Document Date, Name, Vat Office, Vat No to the first table.

Between first and second table, Description column is same.
Between second and fourth table, Vendor No column is same.
Between third and fifth table, Customer No column is same.

I gave it a try a few times but i couldn't figure it out.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:52:01

you just want to join all the table together based on columns they've in common. Please try it out first by reading concept of joins as its straightforward. In case you face any issue, post and we will help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

quanom
Starting Member

4 Posts

Posted - 2011-12-13 : 09:27:49

I can combine three tables but couldn't combine 5 tables because it is a two-way join. Here is the combining for the customer tables: Table 1, Table 3 and Table 5:

SELECT [Table 1].[Posting Date], [Table 1].[Account No], [Table 1].[Document No], [Table 1].Description, [Table 1].Amount, [Table 3].[Document Date], [Table 5].Name, [Table 5].[Vat Office], [Table 5].[Vat No]

FROM ([Table 1] LEFT JOIN [Table 3] ON [Table 1].Description = [Table 3].Description) LEFT JOIN [Table 5] ON [Table 3].[Customer No] = [Table 5].[Customer No]

WHERE ((([Table 1].[Posting Date]) Between #1/1/2009# And #12/31/2009#) AND (([Table 1].[Account No]) Between "1911001001" And "1911001005"))

GROUP BY [Table 1].[Posting Date], [Table 1].[Account No], [Table 1].[Document No], [Table 1].Description, [Table 1].Amount, [Table 3].[Document Date], [Table 5].Name, [Table 5].[Vat Office], [Table 5].[Vat No];

I couldn't find how to add vendor tables to this and create a table with same amount of rows with vendor + customer infos.

quanom
Starting Member

4 Posts

Posted - 2011-12-13 : 10:17:38

I uploaded a screenshot to explain what I need to do:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 10:45:21

looks like below is what you want
if not post some sample data to show table relationships


SELECT t.[Posting Date],
COALESCE(m.[Document Date],n.[Document Date]) AS [Document Date],
t.[Document No],
t.[Description],
COALESCE(m.Name,n.Name) AS Name, 
COALESCE(m.[Vat Office],n.[Vat Office]) AS [Vat Office], 
COALESCE(m.[Vat No],n.[Vat No]) AS [Vat No],
t.Amount
FROM table1 t
LEFT JOIN 
(SELECT t2.Description, t4.Name, t4.[Vat Office], t4.[Vat No]
FROM table2 t2
INNER JOIN table4 t4
ON t4.[Vendor No] = t2.[Vendor No]
)m
on m.Description = t.Description
LEFT JOIN 
(SELECT t3.Description, t5.Name, t5.[Vat Office], t5.[Vat No]
FROM table3 t3
INNER JOIN table5 t5
ON t5.[Customer No] = t3.[Customer No]
)n
ON n.Description = t.Description

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

quanom
Starting Member

4 Posts

Posted - 2011-12-16 : 02:36:03

It seems that Access 2010 doesn't support COALESCE syntax.

What can I use instead of COALESCE?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 03:19:19

you can use ISNULL() also

One more thing, if you're looking at Access query please post in access forum. you've post this in ms sql server forum currently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources