Join Query Help

Thank you for your help. I hope this makes sense.
I have a table that contains 8 rows. Each row describes a phone type
(office, voice, etc) with a primary key and description, thats all.
Another table contains customer rows (first name, last, etc), and a primary
key, customerid.
A third table contains phone numbers as they relate to customers. A PhoneId
field is the primary key for this table. The customerid and the phone type
primary key are foreign keys in this table.
A customer can have many phone types. I am trying to write a query that gets
the existing phone types with phone, along with those phone types that the
customer does NOT have. My WHERE clause in this query is what I think is
breaking it.
A full outer join is not accomplishing this. I only get those numbers that
already are assigned to the customer. Since there are 8 types of numbers I
expect 8 rows.
SELECT
p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
FROM Phones ph
FULL OUTER JOIN PhoneTypes p
on ph.PhoneTypeFK = p.PhoneTypePK
WHERE ph.CustomerID = 12345
How can I get all 8 rows?never mind guys... I got it as soon as I posted it. I am now doing an inner
query that returns the phone numbers assigned and FULL OUTER JOINing that
with an outer query that retrieves all my phone types on the PhoneTypeFK.
Thanks
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>|||even better - a simple left join does the trick.
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>