INNER JOIN Multi 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

Other Forums

MS Access

INNER JOIN Multi tables

Author

Topic

rabiaah
Starting Member

5 Posts

Posted - 2008-07-03 : 06:15:30

Dear Guys,

I have the same problem with the following statement:

SELECT Contact.ContactIDE, Contact.ContactName, tblInCash.CashTotal AS tblInCash, tblInInvoice.InvoiceTotal AS tblInInvoice
FROM Contact INNER JOIN
(SELECT ContactIDE,SUM(CashTotal) AS cashres
FROM tblInCash AS CashTable GROUP BY ContactIDE) AS tblInCash ON Contact.ContactIDE=tblInCash.ContactIDE INNER JOIN
(SELECT ContactIDE,SUM(InvoiceTotal) as invoiceres
FROM tblInInvoice AS InvoiceTable
GROUP BY ContactIDE) AS tblInInvoice ON Contact.tblContactIDE=tblInInvoice.ContactIDE

Now, My Quistion as follow:

I have 5 Tables,
1- For the contact details with ContactIDE
2- Cash Table also with ContactIDE
3- Checks Table also with ContactIDE
4- Credit Table also with ContactIDE
5- Invoice Table also with ContactIDE

I tried the statement between three tables: Contact & tblInCash & tblInInvoice.

I need from those tables to Calculate the sum of the Incoming as Cash and the Outgoing in the Invoice then if the SUM of tblInCash smaller than the SUM of tblInInvoice then I will display the Contact details.

As a one sentense:
I need to display the Contacts that have to pay me.

So, I need a help here to try fix what's wrong in this SQL select statement.

---------------------------------------------------------------
Important Note: I'm using VB6.0 with MDB File for this example.

Regards...

Rabia :)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-03 : 06:32:27

I am guessing that you are getting an error due to the field names in your first query.

Try this:


SELECT Contact.ContactIDE, Contact.ContactName, tblInCash.cashres AS tblInCash, tblInInvoice.invoiceres AS tblInInvoice
FROM Contact INNER JOIN
(SELECT ContactIDE,SUM(CashTotal) AS cashres
FROM tblInCash AS CashTable GROUP BY ContactIDE) AS tblInCash ON Contact.ContactIDE=tblInCash.ContactIDE INNER JOIN
(SELECT ContactIDE,SUM(InvoiceTotal) as invoiceres
FROM tblInInvoice AS InvoiceTable
GROUP BY ContactIDE) AS tblInInvoice ON Contact.tblContactIDE=tblInInvoice.ContactIDE

rabiaah
Starting Member

5 Posts

Posted - 2008-07-03 : 06:40:05

I got the following Error:

Syntax Error ( missing operator ) in query expression.

But anyway, can anyone try help me to figure out the following request:

I need to Calculate the Cash and Checks and Credit as One Sum
then to calculate the SUM of the Invoices.

if the first total smaller than the Invoices SUM then I have to display the Customer details from the Contact table.

My problem that maybe in those tables the ContactIDE not exist but I still need to get the SUM results from all those tables.

Is there any examples that could help me to proceed on.

Regards ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources