LEFT OUTER JOIN 1

[Laeg]

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]

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]

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

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]

no, sorry, now i'm lost

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

 

[PHV]

Typed, untested:

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]

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