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
 Join two select queries from same table

Author  Topic 

Colweb
Starting Member

4 Posts
Posted - 2012-07-05 : 09:26:37
Credit to USER:visakh16 that drafted the following select statements:

First Select Query: Lists the Address and Contact details of the PC (Primary Contact)
SELECT STUDENT_NUMBER AS STUDENT_NO,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE1 END) AS PCPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE2 END) AS PCPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE3 END) AS PCPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE4 END) AS PCPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_POSTCODE END) AS PCPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE1 END) AS PCSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE2 END) AS PCSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE3 END) AS PCSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE4 END) AS PCSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_POSTCODE END) AS PCSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_LINE1 END) AS PCCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' THEN ADDRESS_LINE1 END) AS PCHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' THEN ADDRESS_LINE1 END) AS PCET,
MAX(CASE WHEN ADDRESS_CODE ='ET' THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' THEN ADDRESS_LINE1 END) AS PCWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' THEN ADDRESS_CODE END) AS CTYPE6
FROM N24vaddress_reg
WHERE STUDENT_NUMBER > '200900000'
AND CONTACT_CODE = 'PC'
AND ADDRESS_CODE IN ('PA','SA','CN', 'HT', 'ET', 'WT')
GROUP BY STUDENT_NUMBER
ORDER BY STUDENT_NUMBER;

Second Select Query: Lists the Address and Contact details of the AC (Account Contact)
SELECT STUDENT_NUMBER AS STUDENT_NO,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE1 END) AS ACPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE2 END) AS ACPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE3 END) AS ACPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE4 END) AS ACPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_POSTCODE END) AS ACPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE1 END) AS ACSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE2 END) AS ACSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE3 END) AS ACSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE4 END) AS ACSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_POSTCODE END) AS ACSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_LINE1 END) AS ACCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' THEN ADDRESS_LINE1 END) AS ACHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' THEN ADDRESS_LINE1 END) AS ACET,
MAX(CASE WHEN ADDRESS_CODE ='ET' THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' THEN ADDRESS_LINE1 END) AS ACWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' THEN ADDRESS_CODE END) AS CTYPE6
FROM N24vaddress_reg
WHERE STUDENT_NUMBER > '200900000'
AND CONTACT_CODE = 'AC'
AND ADDRESS_CODE IN ('PA','SA','CN', 'HT', 'ET', 'WT')
GROUP BY STUDENT_NUMBER
ORDER BY STUDENT_NUMBER;

Question: Is there a way to join the two so that the output of all the fields appears on one line for each Student?
Your help will be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 09:37:07
yep extend the earlier suggestion in same way


SELECT STUDENT_NUMBER AS STUDENT_NO,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC' THEN ADDRESS_LINE1 END) AS PCPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE2 END) AS PCPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE3 END) AS PCPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE4 END) AS PCPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_POSTCODE END) AS PCPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE2 END) AS PCSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE3 END) AS PCSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE4 END) AS PCSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_POSTCODE END) AS PCSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCET,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE6,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'  THEN ADDRESS_LINE1 END) AS ACPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE2 END) AS ACPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE3 END) AS ACPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE4 END) AS ACPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_POSTCODE END) AS ACPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE2 END) AS ACSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE3 END) AS ACSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE4 END) AS ACSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_POSTCODE END) AS ACSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACET,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE6
FROM N24vaddress_reg
WHERE STUDENT_NUMBER > '200900000'
AND CONTACT_CODE IN  ('PC','AC')
AND ADDRESS_CODE IN ('PA','SA','CN', 'HT', 'ET', 'WT')
GROUP BY STUDENT_NUMBER
ORDER BY STUDENT_NUMBER;



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2012-07-05 : 09:38:11
with cte1 as
(<first query>)
, cte2 as
(<second query>)
select *
from cte1
full outer join cte2
on cte1.STUDENT_NO = cte2.STUDENT_NO

You shoould also consider
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE1 else '' END)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Colweb
Starting Member

4 Posts
Posted - 2012-07-05 : 11:15:13
It works perfectly... thank you... thank you!
quote:
Originally posted by visakh16

yep extend the earlier suggestion in same way


SELECT STUDENT_NUMBER AS STUDENT_NO,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC' THEN ADDRESS_LINE1 END) AS PCPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE2 END) AS PCPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE3 END) AS PCPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE4 END) AS PCPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_POSTCODE END) AS PCPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE2 END) AS PCSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE3 END) AS PCSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE4 END) AS PCSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_POSTCODE END) AS PCSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCET,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='PC'  THEN ADDRESS_LINE1 END) AS PCWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='PC'  THEN ADDRESS_CODE END) AS CTYPE6,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'  THEN ADDRESS_LINE1 END) AS ACPAL1,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE2 END) AS ACPAL2,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE3 END) AS ACPAL3,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE4 END) AS ACPAL4,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_POSTCODE END) AS ACPACODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACSAL1,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE2 END) AS ACSAL2,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE3 END) AS ACSAL3,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE4 END) AS ACSAL4,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_POSTCODE END) AS ACSACODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACCN,
MAX(CASE WHEN ADDRESS_CODE ='CN' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE3,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACHT,
MAX(CASE WHEN ADDRESS_CODE ='HT' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE4,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACET,
MAX(CASE WHEN ADDRESS_CODE ='ET' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE5,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='AC'   THEN ADDRESS_LINE1 END) AS ACWT,
MAX(CASE WHEN ADDRESS_CODE ='WT' AND CONTACT_CODE='AC'   THEN ADDRESS_CODE END) AS CTYPE6
FROM N24vaddress_reg
WHERE STUDENT_NUMBER > '200900000'
AND CONTACT_CODE IN  ('PC','AC')
AND ADDRESS_CODE IN ('PA','SA','CN', 'HT', 'ET', 'WT')
GROUP BY STUDENT_NUMBER
ORDER BY STUDENT_NUMBER;



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 11:34:55
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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