Joining tables in SQL - 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)

Joining tables in SQL

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-11 : 08:01:35

I need to get all record that show LINE_01 > $50K but not in TB3 table. I was not sure how to join these three together. I thought I would join table 1 (TB1) and table 2(TB2) and using CAST FUNCTION to join SOC_SEC_NUM (TB2) and ID (TB3) together. But I worked around...I still don't get it run...Please help...

My first attemp query:
SELECT A.ROOTKEY, A.LINE_01, B.SOC_SEC_NUM
FROM DBA.TB1 A
INNER JOIN DBA.TB2 B ON B.ROOTKEY=A.ROOTKEY
LEFT JOIN DBA.YEAR07 C ON C.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR06 D ON D.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR05 E ON E.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR04 F ON F.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR03 G ON G.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))

WHERE A.LINE_01>50000
AND C.ID IS NULL
AND D.ID IS NULL
AND E.ID IS NULL
AND F.ID IS NULL
AND G.ID IS NULL

ORDER BY 2

Thank you,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 08:07:37

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
and then repost your question.

And when you do repost your question, please make sure you have read this blog post first!
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 08:34:14

How can LINE01 BOTH contain a numeric value of 50000 dollars, and also be an identity value?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 08:37:07

[code]SELECT a.ROOTKEY,
a.LINE_01,
b.SOC_SEC_NUM
FROM DBA.TB1 AS a
INNER JOIN DBA.TB2 AS b ON b.ROOTKEY = a.ROOTKEY
LEFT JOIN (
SELECT ID
FROM DBA.YEAR07

UNION ALL

SELECT ID
FROM DBA.YEAR06

UNION ALL

SELECT ID
FROM DBA.YEAR05

UNION ALL

SELECT ID
FROM DBA.YEAR04

UNION ALL

SELECT ID
FROM DBA.YEAR03
) AS x ON x.ID = a.LINE_01
WHERE a.LINE_01 > 50000
ORDER BY a.LINE_01[/code]

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

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 08:50:53

quote:
Originally posted by Peso

How can LINE01 BOTH contain a numeric value of 50000 dollars, and also be an identity value?

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

I believe line_01 is numeric datatype. so you may join TB2.SOC_SEC_NUM with YEAR07.ID using cast function.

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 09:12:11

quote:

Originally posted by Peso

SELECT		a.ROOTKEY,
		a.LINE_01,
		b.SOC_SEC_NUM
FROM		DBA.TB1 AS a
INNER JOIN	DBA.TB2 AS b ON b.ROOTKEY = a.ROOTKEY
LEFT JOIN	(
			SELECT	ID
			FROM	DBA.YEAR07

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR06

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR05

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR04

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR03
		) AS x ON x.ID = CAST(B.SOC_SEC_NUM AS VARCHAR(9))
WHERE		a.LINE_01 > 50000
ORDER BY	a.LINE_01

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

Can I just add the cast function in Peso statement...see if it work...see blue line on Peso statement.

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-11 : 09:25:32

It does not work...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 09:35:27

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

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-11 : 12:40:50

quote:

Originally posted by Peso

SELECT		a.ROOTKEY,
		a.LINE_01,
		b.SOC_SEC_NUM
FROM		DBA.TB1 AS a
INNER JOIN	DBA.TB2 AS b ON b.ROOTKEY = a.ROOTKEY
LEFT JOIN	(
			SELECT	ID
			FROM	DBA.YEAR07

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR06

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR05

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR04

			UNION ALL

			SELECT	ID
			FROM	DBA.YEAR03
		) AS x ON x.ID = CAST(B.SOC_SEC_NUM AS VARCHAR(9))
WHERE		a.LINE_01 > 50000
ORDER BY	a.LINE_01

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

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-11 : 12:44:35

This query is really working, however, I need to tweak around with my requirements. thanks,
[/quote]

My final query:
SELECT DISTINCT A.ROOTKEY, A.LINE_01,
B.SOC_SEC_NUM

FROM DBA.TB1 A
LEFT JOIN DBA.TB2 B ON B.ROOTKEY=A.ROOTKEY
LEFT JOIN DBA.YEAR07 C ON C.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR06 D ON D.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR05 E ON E.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR04 F ON F.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))
LEFT JOIN DBA.YEAR03 G ON G.ID=CAST(B.SOC_SEC_NUM AS VARCHAR(9))

WHERE A.LINE_01 > 50000
AND C.ID IS NULL
AND D.ID IS NULL
AND E.ID IS NULL
AND F.ID IS NULL
AND G.ID IS NULL

ORDER BY 2

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-06-11 : 22:02:58

CHEERS...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources