locked
Select the same field from a Table more than once RRS feed

  • Question

  • User-1506996564 posted

    Hello

    I have a table to store Contacts (ID, Name, ....)

    ID: is the primary key

    In another Table I store a School data (SchoolID, SchoolName, Supervisor, Manager, ....)

    Supervisor and Manager are foreign keys (connected to the previous table)

    I need to select data from this table for both Supervisor and Manager in one select statement

    any Ideas Please

    thank you

    Sunday, April 19, 2015 6:15 AM

Answers

  • User281315223 posted

    You could use an JOIN to accomplish this (if I understand you correctly) :

    SELECT c.ID, c.Name, s.Supervisor, s.Manager
      FROM Contacts c
      JOIN Schools s
        ON c.ID = s.SchoolID

    You can see a working example here or demonstrated below :

    If this isn't what you are looking for, you could provide some additional information about exactly what your schema looks like and possible some example INPUT / OUTPUT?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 19, 2015 8:33 AM
  • User281315223 posted

    I believe I may have misunderstood you upon thinking about it a bit more. What you may have wanted to accomplish would be to use a subquery to pull the respective information for each of the Manager and Supervisor as follows :

    SELECT SchoolName, 
           (SELECT Name FROM Contacts c WHERE c.ID = Supervisor) AS Supervisor,
           (SELECT Name FROM Contacts c WHERE c.ID = Manager) AS Manager
      FROM Schools

    You can see an example of this here or demonstrated below :

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 19, 2015 8:38 AM

All replies

  • User281315223 posted

    You could use an JOIN to accomplish this (if I understand you correctly) :

    SELECT c.ID, c.Name, s.Supervisor, s.Manager
      FROM Contacts c
      JOIN Schools s
        ON c.ID = s.SchoolID

    You can see a working example here or demonstrated below :

    If this isn't what you are looking for, you could provide some additional information about exactly what your schema looks like and possible some example INPUT / OUTPUT?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 19, 2015 8:33 AM
  • User281315223 posted

    I believe I may have misunderstood you upon thinking about it a bit more. What you may have wanted to accomplish would be to use a subquery to pull the respective information for each of the Manager and Supervisor as follows :

    SELECT SchoolName, 
           (SELECT Name FROM Contacts c WHERE c.ID = Supervisor) AS Supervisor,
           (SELECT Name FROM Contacts c WHERE c.ID = Manager) AS Manager
      FROM Schools

    You can see an example of this here or demonstrated below :

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 19, 2015 8:38 AM
  • User-1506996564 posted

    Thank you so much for response

    This is my current Query

    SELECT 
           Institute.ID, 
           Institute.Country, 
           Institute.Regeon, 
           Institute.City, 
           Institute.District, 
           Institute.address1, 
           Institute.address2, 
           Institute.email, 
           Institute.mobile, 
           Institute.establishingDate, 
           Institute.gender, 
           Institute.website, 
           Institute.fax, 
           Institute.phone, 
           Institute.name, 
           Institute.Authority, 
           Institute.Sponsor, 
           Institute.realEstateStatus, 
           Institute.buildingType, 
           Institute.Supervisor, 
           Institute.Manager, 
           Institute.academicStaffNoB, 
           Institute.Representative, 
           Institute.studentsCounter, 
           Institute.status, 
           Institute.supportStaffNoW, 
           Institute.supportStaffNoM, 
           Institute.acadimicStaffNoD, 
           Institute.acadimicSaffNoM,

    Persons.name AS Expr1, Persons.mobile AS Expr2, Persons.email AS Expr3 FROM Institute INNER JOIN Persons ON Institute.Supervisor = Persons.ID WHERE (Institute.ID = @ID)

    as you see (Expr1, Expr2, Expr3) are selected from Persons Table as they belong to the Supervisor

    I need to select (Expr4, Expr5, Expr6) for the Manager from the same Table

    (Expr7, Expr8, Expr9) for the Representative from the same Table

    what do I need to change?

    thank you

    Monday, April 20, 2015 3:56 AM
  • User-271186128 posted

    Hi Ghassan,

    As for this issue, you still need to use Inner Join. Like this:

    SELECT 
           Institute.ID, 
           Institute.Country, 
           Institute.Regeon, 
           Institute.City, 
           Institute.District, 
           Institute.address1, 
           Institute.address2, 
           Institute.email, 
           Institute.mobile, 
           Institute.establishingDate, 
           Institute.gender, 
           Institute.website, 
           Institute.fax, 
           Institute.phone, 
           Institute.name, 
           Institute.Authority, 
           Institute.Sponsor, 
           Institute.realEstateStatus, 
           Institute.buildingType, 
           Institute.Supervisor, 
           Institute.Manager, 
           Institute.academicStaffNoB, 
           Institute.Representative, 
           Institute.studentsCounter, 
           Institute.status, 
           Institute.supportStaffNoW, 
           Institute.supportStaffNoM, 
           Institute.acadimicStaffNoD, 
           Institute.acadimicSaffNoM, 
           Persons.name AS Expr1, 
           Persons.mobile AS Expr2, 
           Persons.email AS Expr3 
    
           Manager.columns as Expr4
           Manager.columns as Expr5
           Manager.columns as Expr6
    
    FROM 
           Institute INNER JOIN Persons ON Institute.Supervisor = Persons.ID 
           Institute INNER JOIN Managers ON Institute.Manager = Manager.ID
    
    WHERE (Institute.ID = @ID)

    For more details about Join, please see: https://msdn.microsoft.com/en-us/library/ms191472.aspx

    Best Regards,
    Dillion

    Monday, April 20, 2015 7:35 AM
  • User-1506996564 posted

    Institute INNER JOIN Persons ON Institute.Supervisor = Persons.ID Institute INNER JOIN Managers ON Institute.Manager = Manager.ID

    Thank you for response

    Supervisors, Managers and Representatives are in the same table (Persons)

    I store them their as Contacts or Persons , since they sometime change role

    so a Manager may become a Supervisor later and vice versa

    Monday, April 20, 2015 7:40 AM