SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Location
    Poland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with sql query (join, group, order)

    Hello,
    I'm trying to get all contents with images for section 2, but for one 'id' from 'entry' there should be only one image from 'images' with the lowest 'number'

    As you can see for id=11 there are two images, but I want to get only this with id=28, because it has the lowest number.
    Id 'photos' identifies images from HD.

    Tables.
    'entry'
    Code:
    id | number | content| vis| section
    11	2	op1	1	2
    14	1	op2	0	2
    15	3	op3	1	2
    'images'
    Code:
    id | id_entry | number | vis| 
    21	 11	 2	1
    22	 15	 1	1
    23	 14	 1	1
    28	 11	 1	1
    I was trying with this query, using group by i.id_entry , but i don't know how to sort images by number too.
    Code:
    SELECT i.`id`, i.`vis`, e.`content`
    FROM `images` AS i
    JOIN `entry` AS e ON e.`id` = i.`id_entry`
    WHERE e.`section` = 2 AND e.`vis` = 1
    GROUP BY i.`id_entry`
    ORDER BY e.`number` ASC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT e.id     AS e_id
         , e.number AS e_number
         , e.content
         , i.id     AS i_id
         , i.number AS i_number
         , i.vis    AS i_vis
      FROM entry AS e
    INNER
      JOIN ( SELECT id_entry
                  , MIN(number) AS min_number
               FROM images 
             GROUP
                 BY id_entry ) AS m
        ON m.id_entry = e.id
    INNER
      JOIN images AS i
        ON i.id_entry = e.id
       AND 1.number = m.min_number
     WHERE e.section = 2 
       AND e.vis = 1
    ORDER 
        BY e.number ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2009
    Location
    Poland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thank You very much!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •