CODEWITHSUNDEEP
mysqljoinone-to-many
Ryan Turner
Ryan Turner

Reputation: 3

MySQL select one row, join multiple to it

I have a table named sessions setup like this:

#################################
# set    # timestamp  # session #
#################################
# 5      # 1306458002 # 11      #
# 3      # 1306473234 # 6       #
# 3      # 1305241207 # 3       #
...
#################################

I have a second table named events setup like this:

#######################
# session    # code   #
#######################
# 6          # 45     #
# 6          # -10    #
# 6          # 0      #
# 3          # 7      #
...
#######################

I need to select the latest session of a given set, then join the associated event codes on top of that one record. Most importantly though, I just need this information from suppling a set (here the set is 3):

########
# code #
########
# 45   #
# -10  #
# 0    #
########

Upvotes: 0

Views: 310

Answers (2)

GolezTrol
GolezTrol

Reputation: 116200

select
    e.code
from
    (select
        max(session) as session
    from
        sessions s
    where
        s.set = 3) ms
    inner join event e on e.session = ms.session

Upvotes: 2

ryebr3ad
ryebr3ad

Reputation: 1248

select e.code
from events e
inner join sessions s
    on s.session = e.session
where s.set = <whatever set you want>
and s.timestamp = (select max(timestamp) from sessions where set = s.set)

Upvotes: 0

Related Questions