LEFT OUTER JOIN 1

Laeg · Mar 12, 2009

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

r937 · Mar 12, 2009

Code:

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

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

Laeg · Mar 13, 2009

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!

r937 · Mar 13, 2009

no, sorry, now i'm lost

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

PHV · Mar 13, 2009

Typed, untested:

Code:

SELECT X.xxx, Y.id, Y.xxx, Y.foobar
FROM tblA X INNER JOIN (
SELECT A.key, B.id, B.xxx, B.foobar
FROM tblA A INNER JOIN tblB B ON A.xxx = B.xxx
) Y ON X.key = Y.key

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

Laeg · Mar 13, 2009

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

LEFT OUTER JOIN 1

Laeg

Programmer

r937

Technical User

Laeg

Programmer

r937

Technical User

PHV

MIS

Laeg

Programmer

Similar threads

Part and Inventory Search

Sponsor