Can we join 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

SQL Server 2000 Forums

Transact-SQL (2000)

Can we join multiple tables?

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-23 : 08:46:01

I need to join multiple tables in nested-query...I don't know if we can...Please see below query for information..and help me with the syntax...thanks a bunch!

I got error message "Invalid use aggregate function"...

SELECT a.EIN,
a.STCD,
a.NAME,
a.CTRPTCD,
a.CTSIC,
a.FDATE,
a.STATUS,
a.ctcbal,

FROM DBA.DEBTSH A
left join (select PS_FEIN, count(*)
from dba.tk1
group by PS_FEIN) L on L.PS_FEIN=A.EIN

left join (
select PRIMARY_TIN
FROM DBA.F1065_K1_06

UNION ALL
select PRIMARY_TIN
FROM DBA.F1065_K1_05

UNION ALL
select PRIMARY_TIN
FROM DBA.F1065_K1_04

UNION ALL
select PRIMARY_TIN
FROM DBA.F1065_K1_03

UNION ALL
select PRIMARY_TIN
FROM DBA.F1065_K1_02

UNION ALL
select PRIMARY_TIN
FROM DBA.F1065_K1_01

) M ON M.PRIMARY_TIN=A.EIN
left join (
select PRIMARY_TIN
FROM DBA.F1041_K1_06

UNION ALL
select PRIMARY_TIN
FROM DBA.F1041_K1_05

UNION ALL
select PRIMARY_TIN
FROM DBA.F1041_K1_04

UNION ALL
select PRIMARY_TIN
FROM DBA.F1041_K1_03

UNION ALL
select PRIMARY_TIN
FROM DBA.F1041_K1_02

UNION ALL
select PRIMARY_TIN
FROM DBA.F1041_K1_01
) N on N.PRIMARY_TIN=A.EIN
left join (
select PRIMARY_TIN
FROM DBA.F1120S_K1_06

UNION ALL
select PRIMARY_TIN
FROM DBA.F1120S_K1_05

UNION ALL
select PRIMARY_TIN
FROM DBA.F1120S_K1_04

UNION ALL
select PRIMARY_TIN
FROM DBA.F1120S_K1_03

UNION ALL
select PRIMARY_TIN
FROM DBA.F1120S_K1_02

UNION ALL
select PRIMARY_TIN
FROM DBA.F1120S_K1_01
) O on O.PRIMARY_TIN=A.EIN

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-23 : 08:53:05

quote:
left join (select PS_FEIN, count(*)
from dba.tk1...

You don't need count(*) here.

quote:
select PRIMARY_TIN
FROM DBA.F1065_K1_06, DBA.F1065_K1_05, DBA.F1065_K1_04, DBA.F1065_K1_03, DBA.F1065_K1_01

Where is the join condition between all these tables? This will result in cartesian product.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 09:24:19

You have to name the COUNT(*).

SELECT		a.EIN,
		a.STCD,
		a.NAME,
		a.CTRPTCD,
		a.CTSIC,
		a.FDATE,
		a.STATUS,
		a.ctcbal,
		l.yak
FROM		DBA.DEBTSH AS A
left join	(
			select		PS_FEIN,
					count(*) AS yak
			from		dba.tk1
			group by	PS_FEIN
		) as L on L.PS_FEIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_01

		) as M ON M.PRIMARY_TIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_01
		) as N on N.PRIMARY_TIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_01
		) as O on N.PRIMARY_TIN = A.EIN

E 12°55'05.25"
N 56°04'39.16"

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-23 : 09:44:17

quote:

Originally posted by Peso

You have to name the COUNT(*).

SELECT		a.EIN,
		a.STCD,
		a.NAME,
		a.CTRPTCD,
		a.CTSIC,
		a.FDATE,
		a.STATUS,
		a.ctcbal,
		l.yak
FROM		DBA.DEBTSH AS A
left join	(
			select		PS_FEIN,
					count(*) AS yak
			from		dba.tk1
			group by	PS_FEIN
		) as L on L.PS_FEIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1065_K1_01

		) as M ON M.PRIMARY_TIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1041_K1_01
		) as N on N.PRIMARY_TIN = A.EIN
left join	(
			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_06

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_05

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_04

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_03

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_02

			UNION ALL

			select	PRIMARY_TIN
			FROM	DBA.F1120S_K1_01
		) as O on N.PRIMARY_TIN = A.EIN

E 12°55'05.25"
N 56°04'39.16"

Thanks Peso, it work by itself but when I merge to the big nested-query...I got error message: Invalide Aggregate Function? could you please let me know what I need to do to fix that error...thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 10:06:37

You've show us your full query then to analyse why error occured.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 10:06:43

It it "work by itself" you can't be using MICROSOFT SQL Server.

E 12°55'05.25"
N 56°04'39.16"

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-23 : 10:29:35

quote:
Originally posted by Peso

It it "work by itself" you can't be using MICROSOFT SQL Server.

E 12°55'05.25"
N 56°04'39.16"

I got it run now...Thanks for all your help!...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 10:33:55

What did you change to make it work?

E 12°55'05.25"
N 56°04'39.16"

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-23 : 18:06:50

quote:
Originally posted by Peso

What did you change to make it work?

E 12°55'05.25"
N 56°04'39.16"

The above quote is correct. I was calling a max for one of varible name of big query, but I used a duplicated calling name that was why I got error "Invalid using aggregate function". For example I supposed to call MAX(TRANS_CODE) AS CODE, But I called MAX(TRANS_CODE) AS TRANSCODE (TRANSCODE was used previous in the query). I have a 4 pages long query that was why I missed....:-)

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources