1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL left join, limit 1 row

Discussion in 'MySQL' started by hugl3, Oct 13, 2008.

  1. #1
    Hello all.
    this query works well.
    But I came up with one problem, when I left join photos, I get as many rows as user has photos.

    is there someway to do for example:
    `order by photos.id DESC limit 1`
    In the middle of left join or somehow else ?

    My current SQL query :
    
    SELECT `users`.`id` , `users`.`name` , `cities`.`name` AS `city` , `photos`.`photo` AS photo
    FROM users
    LEFT JOIN `cities` ON users.city = cities.id
    LEFT JOIN `photos` ON users.id = photos.id
    WHERE users.id
    IN ( 77, 165, 166, 169, 164, 194, 195 )
    
    
    Code (markup):
    Thank you guys for your time!
    SEMrush
    cheers,
    Jaroslav
     
    hugl3, Oct 13, 2008 IP
    SEMrush
  2. mwasif

    mwasif Active Member

    Messages:
    818
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Use GROUP BY to limit 1 picture per user

    SELECT `users`.`id` , `users`.`name` , `cities`.`name` AS `city` , `photos`.`photo` AS photo
    FROM users
    LEFT JOIN `cities` ON users.city = cities.id
    LEFT JOIN `photos` ON users.id = photos.id
    WHERE users.id
    IN ( 77, 165, 166, 169, 164, 194, 195 )
    GROUP BY users.id
     
    mwasif, Oct 14, 2008 IP
  3. hugl3

    hugl3 Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hello mwasif.
    I have just spoted that I accidenly did wrote a reply, but it didn't show on the list.

    I wanted to thank you, since it works like it should

    Thanks for you time, and sorry for a delay :)

    Have a good time!
     
    hugl3, Oct 27, 2008 IP
  4. mwasif

    mwasif Active Member

    Messages:
    818
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #4
    You are welcome.
     
    mwasif, Oct 30, 2008 IP