SQL One-To-Many join issue

Question

Let's say I have two tables in Access. TableLetters and TableNumbers. TableLetters has one column TheLetter and 4 records, A, B, C, & D. TheNumbers is many for one TableLetters record. Say we have two columns in TheNumbersTable [TheLetter][TheNumber]. See below:

TheLetters
[TheLetter]
A
B
C
D

TheNumbers
[TheLetter][TheNumber]
A          1
A          2
A          3
B          1
B          2

How do I write a query that returns one record for each "TheLetters" record and the MAX "TheNumber" from TheNumbers table or blank if there's no match for TheLetter in TheNumbers table? So I want my result set to be:

[TheLetters.TheLetter][TheNumbers.TheNumber]
A                     3  
B                     2  
C                     <NULL>
D                     <NULL>

I can get A,3 - B,2 but it cuts out C & D because there's not a match in TheNumbers. I've tried switching my joins all around. I've tried putting an IF in the WHERE clause that says if we have a match return the record from TheNumbers or else give me blank. I can't seem to get the syntax right. Thanks for any help!

lc. · Accepted Answer · 2013-12-12T00:55:19.057

1

The key is to use a LEFT JOIN:

SELECT l.TheLetter, MAX(n.TheNumber)
FROM TheLetters l
LEFT JOIN TheNumbers n ON l.TheLetter = n.TheLetter
GROUP BY l.TheLetter

A left outer join returns all rows in the left table, returning data for any correlated rows in the right table, or a single row with the right table's columns set to NULL if there are no correlated rows.

edited Dec 12 '13 at 00:55

answered Dec 12 '13 at 00:44

lc.

113,939
20
158
187

Thanks I'll try it, should that be "ON l.TheLetter = n.TheLetter"? – DontFretBrett Dec 12 '13 at 00:50
@DontFretBrett Yes it should, sorry. – lc. Dec 12 '13 at 00:55
Ok thanks! That worked. One thing I forgot to ask -- what if "TheNumbers" had one more column and I wanted to return that column value as well, from the same record that was selected by doing MAX(n.TheNumber). I tried doing MAX(n.TheNumber),(SELECT n.NumberString FROM n WHERE n.TheNumber=MAX(n.TheNumber) and n.TheLetter = l.TheLetter) AS NumberString. It said I cannot have an aggregate function in the WHERE clause. Tks! – DontFretBrett Dec 12 '13 at 00:58
That would be a separate question entirely, but take a look at http://stackoverflow.com/questions/3680254/t-sql-selecting-column-based-on-maxother-column and http://stackoverflow.com/questions/4761517/t-sql-select-related-column-data-for-the-max-two-other-columns – lc. Dec 12 '13 at 01:06

score 0 · Answer 2 · answered Dec 12 '13 at 00:55

0

Left Join should correct as below

SELECT l.TheLetter, MAX(n.TheNumber) FROM TheLetters l LEFT JOIN TheNumbers n ON l.TheLetter = n.TheLetter GROUP BY l.TheLetter

answered Dec 12 '13 at 00:55

Anura Adhikari

305
1
10

SQL One-To-Many join issue

2 Answers2