tags:

views:

88

answers:

1

Say i have Four tables

1) Studnets:

  • Student_ID
  • First Name
  • Last Name

2) Contact (Will take the latest item)

  • Contact_ID
  • Address
  • ZipCode
  • DateAdded

3) Phone (Will take the last three items)

  • Contact_ID
  • PhoneNumber
  • DateAdded

4) StudentContactRef

  • Student_ID
  • Contact_ID

How can I query this table? I want to have the fields as shows below:

  • Student_ID
  • First Name
  • Last Name
  • Address
  • ZipCode
  • PhoneNumber1
  • PhoneNumber2
  • PhoneNumber3
+1  A: 
select
    s.Student_ID,
    s.FirstName,
    s.LastName,
    c.Contact_ID,
    c.Address,
    c.ZipCode,
    p.PhoneNumber1,
    p.PhoneNumber2,
    p.PhoneNumber3
from
    Students s
    inner join StudentContactRef r on
        s.Student_ID = r.StudentID
    inner join Contact c on
        r.Contact_ID = c.Contact_ID
    inner join
        (select top 3 Contact_ID, PhoneNumber from Phone 
         pivot (PhoneNumber for PhoneNumber IN 
             (PhoneNumber1, PhoneNumber2, PhoneNumber3) 
         where Contact_ID = r.Contact_ID order by DateAdded desc) p on
        r.Contact_ID = p.Contact_ID

Update: That should get you what you're looking for!

Eric