0

Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint the exact UserID using the UserCities table as a student is linked to a school and a school is linked to a city and UserCities table have linking information for users and cities. You can assume that the given studentID:45 and TagNumber:99.

Users:

UserID, TagNumber

Students:

StudentID, SchoolID

Schools:

SchoolID, CityID

UserCities:

UserID, CityID

I have tried this so far:

select 
    u.UserID
  from
    UserCities uc  inner join School sc on uc.CityID = sc.CityID
    inner join Student st on sc.SchoolID = st.SchoolID
  where
    st.StudentID = 9
  • What SQL have you tried so far? – Philᵀᴹ Jul 27 '16 at 16:28
  • Question updated. I am always confused how to beautifully format my questions. – user1451111 Jul 27 '16 at 16:33
0

I have done it myself.

select 
    userID 
from 
    UserCities
where 
    CityID IN (select CityID 
               from school sc, student st 
               where sc.SchoolID = st.SchoolID and StudentID = 382) 
    and userID IN (select userID 
                   from users 
                   where tagNumber = 4521) ;
  • @ypercubeᵀᴹ how could i have formatted the SQL text myself, like you did ? – user1451111 Jul 29 '16 at 11:16
  • click on the "edit" button and see for yourself! There is a "code sample" button that you can use to format code as code, i.e. padded with 4 spaces in the start of every line. See also help/formatting – ypercubeᵀᴹ Jul 29 '16 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.