×
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

LEFT OUTER JOIN

LEFT OUTER JOIN

LEFT OUTER JOIN

(OP)
Given tblA

id | xxx   | key
 1 |    111 |  747
 2 |    222 |  747

 tblB
 id    | xxx   | foobar
 400 |  111 | a
 401 |  111 | b
 402 |  111 | c

 How do I say for all records in tblA that share the same key please JOIN on tblB and if you find a match duplicate the results out.

 So we'd get a resultset

tblA.xxx   | id    | xxx   | foobar
111        400 |  111     | a
111        401 |  111 | b
111        402 |  111 | c
222        400 |  111 | a
222        401 |  111 | b
222        402 |  111 | c

Thanks for replies

RE: LEFT OUTER JOIN

CODE

SELECT tblA.xxx
     , tblB.id
     , tblB.xxx
     , tblB.foobar
  FROM tblA
INNER
  JOIN tblB
    ON tblB.xxx = tblA.xxx
smile

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: LEFT OUTER JOIN

(OP)
Sorry, this was down to my bad explanation. In the line

"How do I say for all records in tblA that share the same key please JOIN on tblB and if you find a match duplicate the results out" I think I may have confused!

This is not really a simple INNER JOIN as

CODE

SELECT tblA.xxx
     , tblB.id
     , tblB.xxx
     , tblB.foobar
  FROM tblA
INNER
  JOIN tblB
    ON tblB.xxx = tblA.xxx

returns

111        400 |  111 | a
111        401 |  111 | b
111        402 |  111 | c

I wanted the SQL in laymans terms to go, ok I have a record in tblA that has a corresponding record in tblB. This record in tblA shares a key with other records in tblA. So for all corresponding key values I will also return all records in tblB of my first match in tblA. Thus the match will be made on tblA.xxx (id 1), no match will be found for tblA.xxx (id 2) in tblB but seeing as id 1 and id 2 in tblA share the same key id 2 will be given the same tblB results as its key partner id 1, giving us ...

tblA.xxx   | id    | xxx   | foobar
111        400 |  111     | a
111        401 |  111 | b
111        402 |  111 | c
222        400 |  111 | a
222        401 |  111 | b
222        402 |  111 | c

Apologies for the bad explanation!

 

RE: LEFT OUTER JOIN

(OP)
That's exactly it PHV, cheers. I thought I might have just confused the issue more r937, apologies!

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