×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Joining table twice to return two different rows
2

Joining table twice to return two different rows

Joining table twice to return two different rows

(OP)
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 swords
 

RE: Joining table twice to return two different rows

CODE

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

RE: Joining table twice to return two different rows

(OP)
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 swords
 

RE: Joining table twice to return two different rows

Why not simply this ?

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Joining table twice to return two different rows

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  winky smile

r937.com | rudy.ca

RE: Joining table twice to return two different rows

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

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Joining table twice to return two different rows

i think mine's prettier

r937.com | rudy.ca

RE: Joining table twice to return two different rows

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

RE: Joining table twice to return two different rows

(OP)
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 swords
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close