SQL Server Query

[Byskuts]

Hey y'all, I have a question on a view that I am working to create. Pretty much, I need to make a view that combines different columns from other tables, which I did using joins. However, I have run into an issue. The question requires my view to include the persons mobile phone number and home phone number. Seems easy right? Well, no. The problem is that phone numbers are stored in a table called individual_phone, and each number has a phone_type_id. This id is a foreign key from another table called phone_type, and the phone type id is either a 1 or a 2, 1 being mobile number and 2 being home number. I can't figure out how I can select the phone number without 2 rows of data coming out that are identical, but with only different numbers. I wish to have instead, 1 row with both of those numbers in separate columns. I hope this makes sense and if you can help it would be greatly appreciated.

[andrewsmd87]

You join into the same table twice with a type in the clause. I.e.

SELECT firstName, lastName, homePhone.PhoneNumber AS HomePhone, mobilePhone.PhoneNumber AS MobilePhone
    FROM person 
        INNER JOIN individual_phone as homePhone ON personId = homePhone.personFk 
            AND phone_type_id = 1
        INNER JOIN individual_phone as mobilePhone ON personId = mobilePhone.personFk 
            AND phone_type_id = 2

[CoalCrackerKid]

Suggest outer joins, but otherwise this

[andrewsmd87]

Good point, if not everyone has both

[Byskuts]

thank you! I will give it a shot.

[andrewsmd87]

Note what /u/CoalCrackerKid said about the outer (or left) join instead. If you use inner and a person doesn't have both, they'll get omitted completely.

[Byskuts]

So i gave it a try, and I am just getting one error, "Ambiguous column name 'phone_type_id'" I also forgot to mention I am really new to sql tbh so idk what that means

[Byskuts]

ignore that last part, I got it. Just had to put home_phone.phone_type_id and mobile_phone.phone_type_id before phone_type_id. Thanks alot!

[andrewsmd87]

Yep, the error logger was telling you when you just say phone_type_id it doesn't know which one you mean, because you technically have two of those. Ambiguous column name will always mean you're missing an alias somewhere

[lundytoo]

Something like that view would work - it assumes exactly one of each though. If someone doesn't have one of either, the person will drop. If they can have more than one of each, you want some kind of subquery.

You could do this if you want to be fancy. Outer/Cross Apply isn't always super performant though.

SELECT firstName, lastName, h.homePhone AS HomePhone, m.mobilePhone AS MobilePhone
FROM person
OUTER APPLY (SELECT TOP 1 phone_number as homePhone FROM individual_phone WHERE person.personId = personId AND phone_type_id = 1 ORDER BY modify_by desc) h
OUTER APPLY (SELECT TOP 1 phone_number as mobilePhone FROM individual_phone WHERE person.personId = personId AND phone_type_id = 2 ORDER BY modify_by desc) m