If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Joining tables
Hi
I have two tables - one for debtors and one for creditors, both have the same columns (name, number, NIP, value). I want to create a table (query) that will combine rows from both tables but only those rows that have the same NIP. Furthermore I want the rows to be grouped by the name with sums on values. I tried this query: SELECT Debtors.name, Debtors.NIP, SUM(Debtors.values) as [Total debts] FROM Debtors GROUP BY Debtors.name, Debtors.NIP UNION ALL SELECT Creditors.name, Creditors.NIP, SUM(Creditors.values) FROM Creditors GROUP BY Creditors.name, Creditors.NIP HAVING Creditors.NIP = Debtors.NIP; but it won't run. The having clause is incorrect, probably. Any ideas? Regards IgorM |
#2
|
|||
|
|||
Joining tables
IgorM wrote:
I have two tables - one for debtors and one for creditors, both have the same columns (name, number, NIP, value). I want to create a table (query) that will combine rows from both tables but only those rows that have the same NIP. Furthermore I want the rows to be grouped by the name with sums on values. I tried this query: SELECT Debtors.name, Debtors.NIP, SUM(Debtors.values) as [Total debts] FROM Debtors GROUP BY Debtors.name, Debtors.NIP UNION ALL SELECT Creditors.name, Creditors.NIP, SUM(Creditors.values) FROM Creditors GROUP BY Creditors.name, Creditors.NIP HAVING Creditors.NIP = Debtors.NIP; but it won't run. The having clause is incorrect, probably. SELECT [name], NIP, SUM(values) as [Total debts] FROM (SELECT Debtors.name, Debtors.NIP, Debtors.values FROM Debtors INNER JOIN Creditors On Debtors.NIP = Creditors.NIP UNION ALL SELECT Creditors.name, Creditors.NIP, Creditors.values FROM Creditors INNER JOIN Debtors On Creditors.NIP = Debtors.NIP) AS Qry1 GROUP BY [name], NIP |
Thread Tools | |
Display Modes | |
|
|