SQL with same fields twice diff Id

Good Day Experts!

I am having a bit of trouble with a SQL query I need to write.  It "feels" like the desired result is possible but I am not sure how to get there.

I have a Person table with id and firstname lastname fields.  I need to display firstname lastname twice but for different id's.  One is for the student id and the other for the teacher id.

Can you help point me in the right direction?

Thanks,
Ed
Jimbo99999Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Is the relationship information in this one table (meaning, does the student record have the teacher id in it)?  Or is there another table that has that info?

You will need to do a join of some sort, but it depends on how you data is structured...


»bp
0
YZlatCommented:
Can you post your table structures? I am assuming techer ID and Student ID are in separate tables?
0
Jimbo99999Author Commented:
Hello thanks for taking the time to help.

Person
     id
     firstname
     lastname

Section
    teacherID

Enrollment
    personID

I can do the joins and make the Section.teacherID and Enrollment.personID visible to the Person table without any trouble.

I want to be able to display the following in the same record:
     Person.firstname as 'Student firstname',
     Person.lastname as 'Student lastname,
     Person.firstname as 'Teacher firstname',
     Person.lastname as 'Teacher lastname

Thanks,
Ed
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewCommented:
You still haven't shown a table(s) where we can join to relate students to teachers.


»bp
0
YZlatCommented:
How are the tables joined?
0
Jimbo99999Author Commented:
I apologize, I am not sure what you are asking for.  

Person.id = Section.teacherID
  and
Person.id = Enrollment.personID
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is something like this that you're looking for?
SELECT P.id StudentID, E.firstname StudentFirstName, E.lastname StudentLastName,
		P.id TeacherID, S.firstname TeacherFirstName, S.lastname TeacherLastName
FROM Person P
	LEFT JOIN Section S ON P.id = S.teacherID
	LEFT JOIN Enrollment E ON P.id = E.personID

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Or is more like this?
SELECT 'Student', P.id, E.firstname, E.lastname
FROM Person P
	INNER JOIN Enrollment E ON P.id = E.personID
UNION ALL
SELECT 'Teacher', P.id, S.firstname, S.lastname
FROM Person P
	INNER JOIN Section S ON P.id = S.teacherID

Open in new window

0
Jimbo99999Author Commented:
Hello...the first one is more what I ma looking to do.  

But the name fields are in the Person table not in the Section and Enrollment tables:

Person
     id
     firstname
     lastname

So I would have the names twice coming from the Person table which contains all students and all teachers.  

Person.id = Section.teacherID
    Person.firstname as 'Teacher FirstName'
    Person.lastname as 'Teacher LastName'
and
Person.id = Enrollment.personID
    Person.firstname as 'Student FirstName'
    Person.lastname as 'Student LastName'
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you provide some data sample from the tables?
0
YZlatCommented:
try something like this:

SELECT 'Student', e.personID, p.firstname, p.lastname
FROM Enrollment as e 
LEFT JOIN Person as p ON e.personID = p.id
UNION
SELECT 'Teacher', s.teacherID, p.firstname, p.lastname
FROM Section as s
LEFT JOIN Person as p ON s.teacherID = p.id

Open in new window

0
Jimbo99999Author Commented:
I can't do that yet as my id has not been created.  I am just looking at some notes that were left for the position and some diagrams.

After all that I have looked at today,  here is my best effort at what the data is going to look like.

Person
   id = 100
   firstname = Joe
   lastname = Smith
   id = 500
   firstname = Sarah
   lastname = Cooper

Section.teacherID = 500
Enrollment.personID = 100

Person.id = Section.teacherID
    Person.firstname as 'Teacher FirstName' (Sarah)
    Person.lastname as 'Teacher LastName' (Cooper)
and
Person.id = Enrollment.personID
    Person.firstname as 'Student FirstName' (Joe)
    Person.lastname as 'Student LastName'  (Smith)

So, if I join Section and Enrollment to Person the dilemma is how to get the right Person.firstname and Person.lastname labelled as Student or labelled as Teacher.  

This would have been probably better if Teachers and Students were in separate tables.
0
Jimbo99999Author Commented:
YZlat thank you for your response.  That would be great if I could do that, but the Teacher and Student are only a few of the many fields that would need displayed.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you're still in the design phase and already thinking in the queries?
That's why you're having issues and going for a bad database design model. You should design your database model without thinking in queries. When the database model is good, the queries will come easily.

For you case, forget your proposed solution. That will not work and will make you have complex queries.
Separate the data. You can do that with 2 options:
  1. Keep the Person table and add a PersonType field to identify if the person is Student or Teacher.
  2. Create two identical tables (id, firstname, lastname) and give one the name of Student and the other Teacher. Later you might find fields that only makes sense to stay in one of the tables.
1
YZlatCommented:
Not sure what you mean and how is that stopping you from using UNION
0
Jimbo99999Author Commented:
It is designed and in use.  I am new to the company and trying to go through some examples of typical data extractions that they gave me.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better wait for your user to have access to the database. Not really helpful if you can't test our suggestions, anyway.
0
Jimbo99999Author Commented:
YZlat, Teacher and Student need to be in the same resulting record not in separate records.
0
Mark WillsTopic AdvisorCommented:
If you have a table with both the personID and TeacherID then it is relatively straight forward.

We need something that links a student to a teacher.

By way of an example... Let's say you have a table named StudentDiary, and that table has the Student (PersonID) and Teacher (TeacherID) and dates and times and other attributes, Then you can use that table as a basis for your query.

How about something like
Select S.personID, SP.firstname as [Student FirstName], SP.lastname as [Student LastName],
       S.teacherID, TP.firstname as [Teacher FirstName], TP.lastname as [Teacher LastName]
from StudentDiary S
inner join Person SP on SP.personID = S.PersonID
inner join Person TP on TP.personID = S.TeacherID

Open in new window

I think the misunderstanding is having that source table that has both ID's somehow associated.

Now, there probably isnt a StudentDiary table, but there will be something that associates Student with Teacher. I think that is what is missing from your scenario.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jimbo99999Author Commented:
Hello, thank you for your responses.  I finally have access to the data.  Mark Wills...I have never used your suggestion of different aliases on the same table. I will it out this morning.  

I don't have to associate teacher with student.  I just didn't know how to display teacher and student names when the id's were both in the same referenced table.  Joining 2 times looks like it will do the job.  Thanks I will keep you posted.
0
Mark WillsTopic AdvisorCommented:
>> I have never used your suggestion of different aliases on the same table.

Even though I didnt state it clearly, that was really the main point. You can essentially create a logical name for a table by using Aliases. Once established, it is like having a different table when referring to the table by its logical name - the table Alias.

No real difference to using column Alias to create a logical name.

Having said that... There is one important difference. SQL needs to be able to identify the source.

So, a table Alias is immediately usable in a query because the physical source is known when you say "from"

The materialised source of the column alias is not really known until the query has been run.

While I can see a logical argument that there isnt really a difference (an Alias is an Alias), apparently, SQL Server doesnt know until the data has been materialised.

Anyway, hope we have been able to give you some new insights :)
0
Jimbo99999Author Commented:
Thank you everyone for your help.  I learned some new "things" from your expert input.

Ed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.