SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Problem with sql query (join, group, order)

Hybrid View

  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 silver trophybronze trophy
    SitePoint Award Recipient r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    38,456
    Mentioned
    34 Post(s)
    Tagged
    1 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
    r937.com | rudy.ca | 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
  •