CODEWITHSUNDEEP
sqlsql-servert-sql
Darren M
Darren M

Reputation: 95

Nested SQL Select Statements

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)

Result form above query for one user

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

List of Students and their detentions

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.

Upvotes: 2

Views: 56

Answers (2)

Fahmi
Fahmi

Reputation: 37473

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)

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

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

Upvotes: 0

Related Questions