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
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
Talk To Other Members
RE: LEFT OUTER JOIN
CODE
, 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
RE: LEFT OUTER JOIN
"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
, 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
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: LEFT OUTER JOIN
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: LEFT OUTER JOIN