In SQL left outer join, how to join first occurrence only?

Question

Consider the following tables:

Table A

Category  Misc
--------  ----
a         fsd
a         wer
b         fgg
c         ghj 
c         yui
c         rty

Table B (maps category to a number)

Category  Num
--------  ---
a         1
b         2
c         3

Desired Outcome

Category  Num  Misc
--------  ---  ----
a         1    fsd
a              wer
b         2    fgg
c         3    ghj
c              yui
c              rty

A simple left outer join will try to map Num to every Category in Table A, but I only want Num to appear once. Can this be done?

Are you sure you want to have the categories multiple times? — GolezTrol, Jul 30 '13 at 19:42
@Phylogenesis: The goal to have Num appear only once per category -- it doesn't have to be joined on the first occurrence of the category. — Gilead, Jul 30 '13 at 19:46
How come the same category exists three times in the category table? — GolezTrol, Jul 30 '13 at 19:46
@GolezTrol: It's a simplified representation of my problem. In my real table, Table A actually has other fields associated with Category, and Categories can occur multiple times. — Gilead, Jul 30 '13 at 19:48
It would help if you showed that. This problem is probably easier solved if the records in Category are unique some way or another. — GolezTrol, Jul 30 '13 at 19:49
this is usually done in the display part of the code rather than the query. in Oracle you would use a LAG function. — Randy, Jul 30 '13 at 19:53
see this post for mysql LAG function tricks http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql — Randy, Jul 30 '13 at 19:55

score 3 · Answer 1 · answered Jul 30 '13 at 20:03

I'm still confused about the reason, but with your modification it is quite easily possible. By left joining the same table grabbing any (the lowest in this case) Misc column, you can get a record for only one of the occurrences of each Cateogory in TableA. Then you can left join TableB only on those rows where a record was found in that grouped subquery.

select
  a.Category,
  a.Misc,
  b.Num
from
  TableA a
  left join (
    select 
      Category, 
      min(Misc) as MinMisc 
    from 
      TableA
    group by
      Category) c on c.Category = a.Category and c.MinMisc = a.Misc
  left join TableB b on b.Category = a.Category and mc.Category is not null

`mc.Category` should be `c.Category`. Also, a slight issue is that this will not be exactly the same should the first listed `Category` not have the lowest value for `Misc`. — Phylogenesis, Jul 30 '13 at 20:11

In SQL left outer join, how to join first occurrence only?

1 Answers1