Joining table twice to return two different rows 2

[VBAjedi]

Apologies for my seriously rusty SQL... this should be easy.

I need to return values from two different rows of a left-joined table as two different columns in my SELECT query. Here's a simplified scheme of the two tables in question (I did NOT design this dbase!):

USERS table:
USER_ID | USER_NAME

PHONE_NUMBERS table:
USER_ID | PHONE_TYPE | PHONE_NUMBER

Any given user might have records in PHONE_NUMBERS where PHONE_TYPE = "Home", "Cell", or "Fax"

So I need to return a flattened list as follows:
USER_NAME | CELL_NUMBER | HOME_NUMBER

I tried:
SELECT USERS.USER_NAME, PHONE1.PHONE_NUMBER, PHONE2.PHONE_NUMBER
FROM USERS
LEFT JOIN PHONE_NUMBERS AS PHONE1
LEFT JOIN PHONE_NUMBERS AS PHONE2
WHERE PHONE1.PHONE_TYPE = "Home"
OR PHONE2.PHONE_TYPE = "Cell"

but it returns the same number in both phone columns. I feel like I'm so close...


VBAjedi

 

[r937]

SELECT users.user_name
     , MAX(phone1.phone_number) AS Home_Phone
     , MAX(phone2.phone_number) AS Cell_Phone
  FROM users
LEFT OUTER
  JOIN phone_numbers AS phone1
    ON phone1.user_id = users.user_id
   AND phone1.phone_type = 'Home'
LEFT OUTER
  JOIN phone_numbers AS phone2
    ON phone2.user_id = users.user_id
   AND phone2.phone_type = 'Cell'
GROUP
    BY users.user_name

r937.com | rudy.ca

 

[VBAjedi]

Hey! Thanks for the help. That works, and I feel better because it wasn't as simple as I was thinking it would be. Have a star!

However, I don't quite understand something. Like I said in my first post I simplified the query to focus on the part I was stuck on. I'm actually selecting a whole list of other columns from my "users" table as well as some items from other joined tables. With your approach it only works if I "GROUP BY" each and every value I select (except the two phone fields that you're using MAX on).

If I leave a column out of the GROUP BY section I get the error "Column 'xxx' is invalid because it is not contained in either an aggregate function or the GROUP BY clause". What do I need to read up on to understand what's going on here?

VBAjedi

 

[PHV]

Why not simply this ?

SELECT U.USER_NAME, P1.PHONE_NUMBER CELL_NUMBER, P2.PHONE_NUMBER HOME_NUMBER
  FROM USERS U
  LEFT JOIN PHONE_NUMBERS P1 ON U.USER_ID = P1.USER_ID AND P1.PHONE_TYPE = 'Cell'
  LEFT JOIN PHONE_NUMBERS P2 ON U.USER_ID = P2.USER_ID AND P2.PHONE_TYPE = 'Home'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

PHV, you're wonderful

consider what your query will produce when someone has two Cell phone numbers and/or two Home phone numbers, compared to what my query will produce...

... in the context of the specification by the VBAjedi that it be a "flattened" list ;-)

r937.com | rudy.ca

 

[PHV]

If the user may have several phone numbers of the same type:

SELECT U.USER_NAME, P1.CELL_NUMBER, P2.HOME_NUMBER
  FROM USERS U
  LEFT JOIN (
Select USER_ID, Max(PHONE_NUMBER) CELL_NUMBER From PHONE_NUMBERS Where PHONE_TYPE = 'Cell' Group By USER_ID
) P1 ON U.USER_ID = P1.USER_ID
  LEFT JOIN (
Select USER_ID, Max(PHONE_NUMBER) HOME_NUMBER From PHONE_NUMBERS Where PHONE_TYPE = 'Home' Group By USER_ID
) P2 ON U.USER_ID = P2.USER_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

i think mine's prettier

r937.com | rudy.ca

 

[PHV]

Isn't mine's easier to add more tables ?

 

[VBAjedi]

PHV - I've been absent from the forum for a couple of years - good to see you're still in here helping people in more disciplines than I can keep track of!

You two crack me up. Valid points all around! In this case though the frontend to this dbase prevents people from putting in more than one phone number of a given type, so PHV's first suggestion works great (although it takes me back to that "Doh, why didn't I think of that???" feeling). Have a star, PHV... that one makes it into my query.

I'll leave it up to you two to decide which approach is prettier! They both worked for me.

VBAjedi