Sub select statement - 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

Sub select statement

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-08-23 : 12:43:40

I need to pull only one address per student in my script below. When using distinct for the entire script, the results return a lower number than what I expect. So I thought I could use distinct for just the address in a sub select, but I get an error which states 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I not sure what that means. Here is my code:
select
RIGHT('000000' + s.studentNumber, 6) as 'studentNumber',
s.gender,
s.lastName,
s.firstName,
s.middleName,
ma.phone,
convert(varchar,s.birthdate,101)AS 'birthDate',
(select distinct ma.addressLine1,
STUFF(ma.addressLine2,CHARINDEX(',',ma.addressLine2),LEN(ma.addressLine2),'') City,
LEFT(LTRIM(STUFF(ma.addressLine2,1,CHARINDEX(',',ma.addressLine2),'')),2) State,
RIGHT(RTRIM(ma.addressLine2),5) Zip
From v_MailingAddress ma),
'' as'program',
s.grade,
sc.number as 'school number'
From student s
INNER JOIN school sc on sc.schoolID = s.schoolID
INNER JOIN v_MailingAddress ma ON ma.personID = s.personID
where s.calendarID in (8,12,16,20,24,28,32,36,40,44,48,53,56,60)and s.startYear = '2012'

Being new to SQL Server, I see that there is a way to save your results to a csv file by right clicking on the result table and saving as. This gives me error in my first row, so is there a better way to do this. Can you save it to a tab delimited file as well?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 13:01:02

T-SQL does not let you use the sub-query in the select list the way you have done. It is almost like ANSI syntax, but you will need to correlate and you will need one sub-query for each column. A better option perhaps is to use a CTE or subquery like the (untested) code I am showing below:

SELECT
	RIGHT('000000' + s.studentNumber, 6) AS 'studentNumber',
	s.gender,
	s.lastName,
	s.firstName,
	s.middleName,
	ma.phone,
	CONVERT(VARCHAR, s.birthdate, 101) AS 'birthDate',
	ad.addressLine1,
	ad.City,
	ad.State,
	ad.Zip,
	'' AS 'program',
	s.grade,
	sc.number AS 'school number'
FROM
	student s
	INNER JOIN school sc
		ON  sc.schoolID = s.schoolID
	INNER JOIN v_MailingAddress ma
		ON  ma.personID = s.personID
	OUTER APPLY
	(
	    SELECT
	    	DISTINCT mo.addressLine1,
	    	STUFF(
	    	    mo.addressLine2,
	    	    CHARINDEX(',', mo.addressLine2),
	    	    LEN(mo.addressLine2),
	    	    ''
	    	) City,
	    	LEFT(
	    	    LTRIM(
	    	        STUFF(mo.addressLine2, 1, CHARINDEX(',', mo.addressLine2), '')
	    	    ),
	    	    2
	    	) STATE,
	    	RIGHT(RTRIM(mo.addressLine2), 5) Zip
	    FROM
	    	v_MailingAddress mo WHERE mo.personID = s.personID
	)ad
			
WHERE
	s.calendarID IN (8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 53, 56, 60)
	AND s.startYear = '2012'

For exporting data to a tab-delimited file, perhaps the fastest (and simplest) option is to use BCP. There is documentation and some good examples on this MSDN page: http://msdn.microsoft.com/en-us/library/ms162802.aspx

You can also use Import/Export Wizard, SSIS etc. if that does not do exactly what you need to do.

jpost
Starting Member

43 Posts

Posted - 2012-08-23 : 13:27:15

Thanks, the revisions seemed to do what I needed to do. When you replaced ma.addressLine1 with ad.addressline1, what does that do. I noticed it at the end to the subselect at the end as well. Now I just need to figure out the bcp piece. Thanks again

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 15:21:58

That "ad" is an alias that I used for the subquery (the one that follows the OUTER APPLY. Perhaps adding the optional keyword would make it a little bit more readable:


......
	INNER JOIN v_MailingAddress ma
		ON  ma.personID = s.personID
	OUTER APPLY
	(
	    SELECT
	    	DISTINCT mo.addressLine1,
	    	STUFF(
	    	    mo.addressLine2,
	    	    CHARINDEX(',', mo.addressLine2),
	    	    LEN(mo.addressLine2),
	    	    ''
	    	) City,
	    	LEFT(
	    	    LTRIM(
	    	        STUFF(mo.addressLine2, 1, CHARINDEX(',', mo.addressLine2), '')
	    	    ),
	    	    2
	    	) STATE,
	    	RIGHT(RTRIM(mo.addressLine2), 5) Zip
	    FROM
	    	v_MailingAddress mo WHERE mo.personID = s.personID
	)  AS ad
....

jpost
Starting Member

43 Posts

Posted - 2012-08-27 : 14:55:57

Got it thanks. Can I use and row_number = 1 to only return on record per student number. I am still getting multiple addresses for the same student, which is causing an issue. Suggestions?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-27 : 19:23:00

You can use row_number - it would be something like this:

SELECT * FROM
(
SELECT
	RIGHT('000000' + s.studentNumber, 6) AS 'studentNumber',
	s.gender,
	s.lastName,
	s.firstName,
	s.middleName,
	ma.phone,
	CONVERT(VARCHAR, s.birthdate, 101) AS 'birthDate',
	ad.addressLine1,
	ad.City,
	ad.State,
	ad.Zip,
	'' AS 'program',
	s.grade,
	sc.number AS 'school number',
	ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
FROM
	student s
	INNER JOIN school sc
		ON  sc.schoolID = s.schoolID
	INNER JOIN v_MailingAddress ma
		ON  ma.personID = s.personID
	OUTER APPLY
	(
	    SELECT
	    	DISTINCT mo.addressLine1,
	    	STUFF(
	    	    mo.addressLine2,
	    	    CHARINDEX(',', mo.addressLine2),
	    	    LEN(mo.addressLine2),
	    	    ''
	    	) City,
	    	LEFT(
	    	    LTRIM(
	    	        STUFF(mo.addressLine2, 1, CHARINDEX(',', mo.addressLine2), '')
	    	    ),
	    	    2
	    	) STATE,
	    	RIGHT(RTRIM(mo.addressLine2), 5) Zip
	    FROM
	    	v_MailingAddress mo WHERE mo.personID = s.personID
	) AS ad
			
WHERE
	s.calendarID IN (8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 53, 56, 60)
	AND s.startYear = '2012'
) s
WHERE RN = 1;

You could also try to use a TOP (1) clause instead of the DISTINCT in the OUTER APPLY, but if you have anything else in the outer query that is not the same for all rows - for example, ma.phone, you would end up getting more than one row.

jpost
Starting Member

43 Posts

Posted - 2012-08-28 : 14:27:23

Perfect, I appreciate all the help. That does exactly what I want. One more issue, with the way the query is written, it returns a column labeled RN in my results. Is there a way I can hide this so when I extract the results I don't have to delete the column? Thanks again

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-28 : 14:40:23

Change the SELECT * to SELECT <ColumnList>

jpost
Starting Member

43 Posts

Posted - 2012-09-12 : 10:17:32

So to hide the RN result in my script just use the following:

select <RN> From
(select distinct
RIGHT('000000' + s.studentNumber, 6) as 'studentNumber',
s.lastName,
s.firstName,
sc.number,
s.grade,
se.teacherDisplay as 'homeroom',
convert(varchar,s.birthdate,1)AS 'birthDate',
ma.addressLine1,
STUFF(ma.addressLine2,CHARINDEX(',', ma.addressLine2),LEN(ma.addressLine2),'') City,
LEFT(LTRIM(STUFF(ma.addressLine2, 1, CHARINDEX(',', ma.addressLine2), '')),2) STATE,
RIGHT(RTRIM(ma.addressLine2), 5) Zip,
s.activeYear,
ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
from student s
INNER JOIN school sc on sc.schoolID = s.schoolID
Left JOIN v_MailingAddress ma ON ma.personID = s.personID
JOIN Roster r ON r.personID = s.personID
JOIN Section se ON se.sectionID = r.sectionID
JOIN SectionPlacement sp ON sp.sectionID = se.sectionID
JOIN Period p ON p.periodID = sp.periodID
JOIN Course c ON c.courseID = se.courseID
JOIN Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID
JOIN SchoolYear sy ON sy.endYear = ca.endYear
where s.activeYear =1 and s.calendarID in (8,12,16,20,24,28,32,36,40,44,48,52,56,60) and s.startYear = '2012' and s.endDate is null
and (((p.name = '1' or p.seq ='2') or (c.homeroom = '1'))))s
WHERE RN = 1

jpost
Starting Member

43 Posts

Posted - 2012-09-12 : 10:18:02

I meant hide it in my results

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-13 : 08:43:13

To hide RN column from your result set..............

SELECT studentNumber, gender, lastName, firstName, middleName, phone, birthDate, addressLine1, City, State, Zip, program, grade, [school number]
FROM
(
SELECT
RIGHT('000000' + s.studentNumber, 6) AS 'studentNumber',
s.gender,
s.lastName,
s.firstName,
s.middleName,
ma.phone,
CONVERT(VARCHAR, s.birthdate, 101) AS 'birthDate',
ad.addressLine1,
ad.City,
ad.State,
ad.Zip,
'' AS 'program',
s.grade,
sc.number AS 'school number',
ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
FROM
student s
INNER JOIN school sc
ON sc.schoolID = s.schoolID
INNER JOIN v_MailingAddress ma
ON ma.personID = s.personID
OUTER APPLY
(
SELECT
DISTINCT mo.addressLine1,
STUFF(
mo.addressLine2,
CHARINDEX(',', mo.addressLine2),
LEN(mo.addressLine2),
''
) City,
LEFT(
LTRIM(
STUFF(mo.addressLine2, 1, CHARINDEX(',', mo.addressLine2), '')
),
2
) STATE,
RIGHT(RTRIM(mo.addressLine2), 5) Zip
FROM
v_MailingAddress mo WHERE mo.personID = s.personID
) AS ad

WHERE
s.calendarID IN (8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 53, 56, 60)
AND s.startYear = '2012'
) s
WHERE RN = 1;

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources