Nested SQL Select Statements

Question

I have a number of tables across two databases that I would like to pulll information out of to display in a grid in a web page.

If I run the following sql it works fine and only returns one row:

Select DISTINCT s.STUDENT#, s.SURNAME,s.GIVEN_NAME,ar.EN_EMAIL,ar.CAREGIVER_NO,i.GIVENNAME,i.SURNAME,i.EMAIL,i.EMAIL_BILLING,ax.CORR_PREFERENCE
      from 
            [PCSchool].[dbo].[STUDENT] s
        INNER JOIN  [PCSchool].[dbo].[ALUMREL] ar
            on s.STUDENT# = ar.CHILD#
        LEFT JOIN
                [PCSchool].[dbo].[IDENTITY] i 
            on ar.PARENT# = i.[MEMBER#]
        left join   [PCSchool].[dbo].[ALUMREL_EX] ax
            on ar.parent# = ax.PARENT#
    where  (ar.PARENT# <> ar.FAMILY_HASH) and ax.CORR_PREFERENCE = 1 and ar.EN_EMAIL = 'I' where s.STUDENT#=7282)

What my problem is, I have another table that has a list of students that has a counter in it and a detention type.

I want to be able to pull the above email information from one table based on the Detention table. The StudentCode in the detention table and the Student# in the above table are the same for each student.

I have no idea whether this can be done with SQL or whether I need to do it via server side code.

Fahmi · Accepted Answer · 2018-11-20 05:27:18Z

1

You can try using join of detention table

Select DISTINCT s.STUDENT#, s.SURNAME,s.GIVEN_NAME,ar.EN_EMAIL,ar.CAREGIVER_NO,i.GIVENNAME,i.SURNAME,i.EMAIL,i.EMAIL_BILLING,ax.CORR_PREFERENCE,detentype,detentioncount
      from 
            [PCSchool].[dbo].[STUDENT] s
        INNER JOIN  [PCSchool].[dbo].[ALUMREL] ar
            on s.STUDENT# = ar.CHILD#
        LEFT JOIN
                [PCSchool].[dbo].[IDENTITY] i 
            on ar.PARENT# = i.[MEMBER#]
        left join   [PCSchool].[dbo].[ALUMREL_EX] ax
            on ar.parent# = ax.PARENT#
        left join Detention on Detention.studentcode=s.STUDENT#
    where  (ar.PARENT# <> ar.FAMILY_HASH) and ax.CORR_PREFERENCE = 1 and ar.EN_EMAIL = 'I' where s.STUDENT#=7282)

answered Nov 20, 2018 at 5:27

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren M Over a year ago

Thanks @fa06 I tweaked the SQL a tiny bit and it is giving me exactly what I needed. I did have that join in, but in the incorrect place. Your a genius!!

George Joseph · Accepted Answer · 2018-11-20 05:26:05Z

0

Joining the two results_set would get you want you want?

select a.student#,a.email 
  from (<first_query>) a
  join students b
    on a.student#=b.studentcode

answered Nov 20, 2018 at 5:26

George Joseph

5,93213 silver badges25 bronze badges

Collectives™ on Stack Overflow

Nested SQL Select Statements

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related