Join three 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

SQL Server 2005 Forums

Transact-SQL (2005)

Join three tables

Author

Topic

mvang04
Starting Member

1 Post

Posted - 2008-08-20 : 17:03:22

Okay, so I have been stuck for the last three days so I thought I would post this to see if anyone can assist me with my problem. I have three tables

table1 {receiptNum(pk), date, payor, dept} //receiptNum is primary key

table2 {id(pk), receiptNum, code, amount} //a receipt can have multiple codes with associated amounts to it - kind of like store items

table3 {codeSquence(pk), code, codeDescription, dept} //can have multiple code records
For ex:
{Seq1, DD, text}
{Seq2, DD, text}

table1.receiptNum = table2.receiptNum
while
table2.code= table3.code
and
table3.dept = table1.dept

the problem is that I am getting double the return records or else how ever many records I have for a code per codeSeq - that's how much the field is doubling.

My query:

Select DISTINCT table3.codeDescription AS Desc, table2.code AS Code, COUNT(table2.id) AS Count, SUM(table2.amount) AS Amount FROM table1 INNER JOIN table2 ON table1.receiptNum = table2.receiptNum Where table1.dept = '100'

Thanks in advance!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-20 : 17:04:46

You are missing a GROUP BY. Remove DISTINCT.

Select table3.codeDescription AS Desc, table2.code AS Code, COUNT(table2.id) AS Count, SUM(table2.amount) AS Amount
FROM table1 I
NNER JOIN table2 ON table1.receiptNum = table2.receiptNum
Where table1.dept = '100'
group by table3.codeDescription, table2.code

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources