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

Author  Topic 

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-26 : 22:05:39
How would I write a SQL that joins the following tables together. Below are primary key of those tables that would need to join.

1. Table 1: table1.rootkey=table2.cc_file_no
table1.corres_name <> ''
max(table1.corres_create_dt) as corres_create_dt

2. Table 2: table1.rootkey=table2.cc_file_no
table2.estate_id=table4.estate_id


3. Table 3: table3.case_id=table4.caseid
table3.case_type_id=1
table3.case_type='P'

4. Table 4: table4.estate_id=table2.estate_id
table4.acn_dln in ('101', '102', '201', '202')

I think table 2, 3, 4 need to join toghether as a sub-query under table1. I tried to write the query for above tables, but I got syntax error...don't know how to solve.

SELECT
A.ROOTKEY,
B.LAST_NAME,
B.FIRST_NAME,
A.CORRES_1ST_ADDR,
A.CORRES_2ND_ADDR,
A.CITY,
A.STATE,
A.ZIP_5,
A.PHONE_NUM,
REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
(C.CORRES_NAME, ' ESQ', ''), ' -ESQ', ''), ' ATTY', ''), ' -ATTY', ''), '.', ''), 'JR', ''), 'ATTORNEY', '') AS CORRES_NAME,
MAX(CORRES_CREATE_DT) AS CORRES_CREATE_DT

FROM DBA.ITCORRES A
INNER JOIN DBA.ITGNINFO B ON B.ROOTKEY=B.ROOTKEY

LEFT JOIN (
SELECT CC_FILE_NO, ESTATE_ID, E.CASE_ID, E.CASE_TYPE_ID, E.CALCULATED_TAX,
FROM DBA.TKESTATE C
INNER JOIN DBA.TKCASIT D ON D.ESTATE_ID=C.ESTATE_ID
INNER JOIN DBA.TKCASE E ON E.CASE_ID=D.CASE_ID
WHERE D.ACN_DLN IN ('101', '102', '201', '202')
AND E.STATUS_ID <>2
AND E.CASE_TYPE_ID= 1
GROUP BY CC_FILE_NO, ESTATE_ID, E.CASE_ID, E.CALCULATED_TAX
)ZZ ON ZZ.CC_FILE_NO=A.ROOTKEY

WHERE A.CORRES_TYPE='P'
AND A.CORRES_NAME <> ''
ORDERY BY CORRES_NAME, CORRES_CREATE_DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 09:43:16
may be this:-

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



replace * by actual columns you want.
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-27 : 11:04:27
quote:
Originally posted by visakh16

may be this:-

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



replace * by actual columns you want.


I tried this, but still get syntax error near to "FROM":
SELECT
T1.ROOTKEY,
T5.LAST_NAME,
T5.FIRST_NAME,
CORRES_NAME,
T1.CORRES_1ST_ADDR,
T1.CORRES_2ND_ADDR,
T1.CITY,
T1.STATE,
T1.ZIP_5,
T1.PHONE_NUM,
CORRES_CREATE_DT

FROM
(SELECT rootkey,MAX(corres_create_dt) as corres_create_dt,
replace (replace (replace (replace (replace (replace (replace(replace(replace(replace(replace
(C.CORRES_NAME, ' ESQ', ''),
' ATTY', ''), '-ESQ', ''), '-ATTY',''),'.',''),',', ''),'JR', ''),'ATTORNEY', ''),'-',''), 'III', ''), '&', '')
as CORRES_NAME,
FROM DBA.table1
WHERE corres_name <> ''
AND CORRES_TYPE='P'
GROUP BY rootkey) t1

INNER JOIN DBA.table5 T5 ON T5.ROOTKEY=T1.ROOTKEY
INNER JOIN DBA.table2 t2 ON t1.rootkey=t2.cc_file_no
INNER JOIN DBA.table4 t4 ON t2.estate_id=t4.estate_id
INNER JOIN DBA.table3 t3 ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 11:13:29
remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-27 : 11:27:29
quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-06-27 : 11:57:19
You need to close the parentheses and alias the table

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
FROM table1
WHERE corres_name <> ''
GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')
) AS Table1

Jim
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 12:03:32
quote:
Originally posted by jimf

You need to close the parentheses and alias the table

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
FROM table1
WHERE corres_name <> ''
GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')
) AS Table1

Jim

I've already done that. I think you missed it out
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 12:04:09
quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,

whats the error?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-06-27 : 12:20:30
Oops! misread the query!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 12:37:10
quote:
Originally posted by jimf

Oops! misread the query!

No probs it happens
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-27 : 13:09:03
quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,

whats the error?


The same syntax error "near a 'FROM. I don't see any wrong with that...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-27 : 13:13:01
quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,

whats the error?


The same syntax error "near a 'FROM. I don't see any wrong with that...

can i see full query used by you? i dont think error is in batch given.
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-27 : 13:45:20
quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,

whats the error?


The same syntax error "near a 'FROM. I don't see any wrong with that...

can i see full query used by you? i dont think error is in batch given.


---------
SELECT
T1.ROOTKEY,
T5.LAST_NAME,
T5.FIRST_NAME,
CORRES_NAME,
T1.CORRES_1ST_ADDR,
T1.CORRES_2ND_ADDR,
T1.CITY,
T1.STATE,
T1.ZIP_5,
T1.PHONE_NUM,
CORRES_CREATE_DT
FROM
(SELECT rootkey,MAX(corres_create_dt) as corres_create_dt,
replace (replace (replace (replace (replace (replace (replace(replace(replace(replace(replace(C.CORRES_NAME, ' ESQ', ''),' ATTY', ''), '-ESQ', ''), '-ATTY',''),'.',''),',', ''),'JR', ''),'ATTORNEY', ''),'-',''), 'III', ''), '&', '') as CORRES_NAME,

FROM DBA.TABLE1
WHERE corres_name <> ''
AND CORRES_TYPE='P'
GROUP BY rootkey) t1

INNER JOIN DBA.TABLE5 T5 ON T5.ROOTKEY=T1.ROOTKEY
INNER JOIN DBA.TABLE2 t2 ON t1.rootkey=t2.cc_file_no
INNER JOIN DBA.TABLE4 t4 ON t2.estate_id=t4.estate_id
INNER JOIN DBA.TABLE3 t3 ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-28 : 02:13:58
quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

remove the duplicate from & try

SELECT * FROM
FROM (SELECT rootkey,MAX(corres_create_dt) as corres_create_dt
      FROM table1
      WHERE corres_name <> ''
      GROUP BY rootkey) t1
INNER JOIN table2 t2
ON t1.rootkey=t2.cc_file_no
INNER JOIN table4 t4
ON t2.estate_id=t4.estate_id
INNER JOIN table3 t3
ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')



Still got an error for syntax...thanks,

whats the error?


The same syntax error "near a 'FROM. I don't see any wrong with that...

can i see full query used by you? i dont think error is in batch given.


---------
SELECT
T1.ROOTKEY,
T5.LAST_NAME,
T5.FIRST_NAME,
CORRES_NAME,
T1.CORRES_1ST_ADDR,
T1.CORRES_2ND_ADDR,
T1.CITY,
T1.STATE,
T1.ZIP_5,
T1.PHONE_NUM,
CORRES_CREATE_DT
FROM
(SELECT rootkey,MAX(corres_create_dt) as corres_create_dt,
replace (replace (replace (replace (replace (replace (replace(replace(replace(replace(replace(C.CORRES_NAME, ' ESQ', ''),' ATTY', ''), '-ESQ', ''), '-ATTY',''),'.',''),',', ''),'JR', ''),'ATTORNEY', ''),'-',''), 'III', ''), '&', '') as CORRES_NAME,

FROM DBA.TABLE1
WHERE corres_name <> ''
AND CORRES_TYPE='P'
GROUP BY rootkey) t1

INNER JOIN DBA.TABLE5 T5 ON T5.ROOTKEY=T1.ROOTKEY
INNER JOIN DBA.TABLE2 t2 ON t1.rootkey=t2.cc_file_no
INNER JOIN DBA.TABLE4 t4 ON t2.estate_id=t4.estate_id
INNER JOIN DBA.TABLE3 t3 ON t3.case_id=t4.caseid
WHERE t3.case_type_id=1
AND t3.case_type='P'
AND t4.acn_dln in ('101', '102', '201', '202')


Couple of errors. You dont need , before FROM which is syntactically wrong. ALso you're you are selecting CORRES_NAME in select list after grouping by rootkey. This is not possible unless you apply some aggregate function on CORRES_NAME like MIN or MAX or include it also in your group by. the decision depends on your requirement. If you want help on it please post what exactly are you trying to get inside the subquery.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site