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

INNER JOIN QUESTION

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 11:43:45

Hello Everyone,

I know this is probably an easy question. I would appreciate any help offered.

Here is my code:


USE CHEC
SELECT 
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	Count(*) AS Totals
--INTO [trackapps]
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE],
FROM [SMT_BRANCHES] INNER JOIN [DAT01]
	ON DAT01 = SMTBRANCHES
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	--And DAT01.[_@TP] = ' '
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	[DATE_CONVERSION_TABLE_NEW].MONTH
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051]
COMPUTE sum(count(*))

I am getting an error on the FROM SMT_Branches on the second Inner Join. What am I doing wroing?

Have a great day!

TIA

Kurt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-12 : 11:47:17

[code]FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE],
~~FROM~~ [SMT_BRANCHES] INNER JOIN [DAT01]
ON DAT01 = SMTBRANCHES
[/code]

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 11:50:22

Hello and thanks,

Here is the new error message that I am getting:

Msg 1013, Level 15, State 1, Line 13
Tables or functions 'DAT01' and 'DAT01' have the same exposed names. Use correlation names to distinguish them.

How do I accomplish this?

TIA.

Kurt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-12 : 11:54:24

use table alias


FROM DAT01 d1 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON d1.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE],
FROM [SMT_BRANCHES] INNER JOIN [DAT01] d2
	ON d2.??? = SMTBRANCHES.????

EDIT : remove the FROM

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 13:24:29

Hello,

Here is my code now:


USE CHEC
SELECT 
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	Count(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
FROM [SMT_BRANCHES] INNER JOIN [DAT01] 
	ON SMT_Branches.[BranchTranType] = SMTBRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	--And DAT01.[_@TP] = ' '
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	[DATE_CONVERSION_TABLE_NEW].MONTH
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051]
COMPUTE sum(count(*))

I now get the following error message:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'.

TIA again and of course Have a great day!

Kurt

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-10-12 : 13:26:11

You have the keyword "FROM" repeated 2 times in your query.

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 13:27:55

Hello Dustin,

Thanks I deleted it but now I get the following error message:

Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'SMT_BRANCHES'

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 13:51:38

Thanks,

It works now, I also forgot the _ on SMT_Branches in the last line.

Have a great day everyone!

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-12 : 14:23:22

Hello Again,

How could I sum just the totals by TranType. I know you can sort the columns by DESC. What is the proper syntax to do this?

Thanks,

Kurt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources