SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to limit returned row in left join query

    Code:
    SELECT a.id,
    		a.news_title,
    		a.published_date ,
    		b.thumb_name
    FROM news a
    LEFT OUTER
    JOIN news_image b
    ON b.id = a.id
    WHERE news_type= 1 AND region = 2 
    ORDER BY a.id 
    DESC LIMIT 4
    this is my current query. I want to limit the number of row of thumb_name to 1 and it has to be ACS. How can i put it ?

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by runrunforest View Post
    Code:
    SELECT a.id,
    		a.news_title,
    		a.published_date ,
    		b.thumb_name
    FROM news a
    LEFT OUTER
    JOIN news_image b
    ON b.id = a.id
    WHERE news_type= 1 AND region = 2 
    ORDER BY a.id 
    DESC LIMIT 4
    this is my current query. I want to limit the number of row of thumb_name to 1 and it has to be ACS. How can i put it ?

    Try this,...This should work,

    Code:
    SELECT a.id,
    		a.news_title,
    		a.published_date ,
    		b.thumb_name
    FROM news a
    LEFT OUTER
    JOIN news_image b
    ON b.id = a.id
    WHERE news_type= 1 AND region = 2 
    ORDER BY a.id 
    LIMIT 0,1
    Thank You
    Known is a Drop, Unknown is an Ocean

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry , ignore the previous Query,

    Try this,...This should work,

    Code:
    SELECT a.id,
    		a.news_title,
    		a.published_date ,
    		b.thumb_name
    FROM news a
    LEFT OUTER
    JOIN news_image b
    ON b.id = a.id
    WHERE news_type= 1 AND region = 2 
    ORDER BY a.id 
    group by b.thumb_name
    LIMIT 0,1
    The 'desc' will give the result in descending order. When it is not mentioned , by default the order by column will be sorted in the ascending order.

    Also make sure to mention the news_type and region fields in the where clause with the table name prefixed like a.news_type or a.region
    Thank You
    Known is a Drop, Unknown is an Ocean

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the GROUP BY clause is in the wrong place, and actually shouldn't be included at all

    besides, i think runrun wants one thumb per news item, not one thumb overall
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, R937 understands me right. Can you suggest solution?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hey, man, where did you go for six weeks???

    what does "it has to be ACS" mean?

    please do a SHOW CREATE TABLE for both tables
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `news` (
      `id` int(11) NOT NULL auto_increment,
      `news_title` varchar(555) collate utf8_unicode_ci NOT NULL,
      `news_detail` varchar(10000) collate utf8_unicode_ci NOT NULL,
      `news_source` varchar(555) collate utf8_unicode_ci NOT NULL,
      `published_date` int(11) NOT NULL,
      `region` tinyint(4) NOT NULL,
      `news_type` int(111) NOT NULL,
      `link` varchar(333) collate utf8_unicode_ci NOT NULL,
      `country` varchar(333) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=59 ;


    Code MySQL:
    CREATE TABLE IF NOT EXISTS `news_image` (
      `id` int(11) NOT NULL,
      `image_name` varchar(444) collate utf8_unicode_ci NOT NULL,
      `thumb_name` varchar(444) collate utf8_unicode_ci NOT NULL,
      `image_source` varchar(111) collate utf8_unicode_ci NOT NULL,
      `image_comment` varchar(111) collate utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    The query i provided in the first post will return four news sets, each news set will come with as many thumb_name as available, which's not good, I want each news set will come with only one thumb_name, and the only one thumbnail has to be the first one in the row.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by runrunforest View Post
    ... has to be the first one in the row.
    unfortunately, rows in a relational table have no sequence, so there is no such thing as "first"

    it can only be based on the value of some column

    shortest thumb name? lowest image name? something like that...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for my bad english. let me show my data, that would make sense ?


    Code MySQL:
    INSERT INTO `news_image` (`id`, `image_name`, `thumb_name`, `image_source`, `image_comment`, `priority`) VALUES
    (39, '1259730015.jpg', '1259730015_thumb.jpg', '', 'Real estate agents believe home owners will be prepared for the rise.', 1),
    (39, '1259730046.jpg', '1259730046_thumb.jpg', '', 'Bob Packer is unfazed by the rise.', 0),

    This is data from the table news_image, as you can see there are two rows with the same id of 39, my query will give back both rows, which is not good, I only need one row, the row where thumb_name = 1259730015_thumb.jpg

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by runrunforest View Post
    I only need one row, the row where thumb_name = 1259730015_thumb.jpg
    why that one?

    okay, let's go with the lowest thumb name per id...
    Code:
    SELECT news.id
         , news.news_title
         , news.published_date 
         , news_image.thumb_name
         , news_image.image_name
      FROM news 
    LEFT OUTER
      JOIN ( SELECT id
                  , MIN(thumb_name) AS this_one
               FROM news_image 
             GROUP
                 BY id ) AS mmmm
        ON mmmm.id = news.id
    LEFT OUTER
      JOIN news_image
        ON news_image.id = news.id
       AND news_image.thumb_name = mmmm.this_one
     WHERE news.news_type= 1 
       AND news.region = 2 
    ORDER 
        BY news.id DESC LIMIT 4
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why that one?
    because... have a visit of the site http://www.diepbachduong.com/index.php/news_real. its how I intend to present the content.

    Will MIN() function take string ?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by runrunforest View Post
    Will MIN() function take string ?
    did you test the query i gave you? what do you think?

    by the way, that site doesn't work
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will try the query other time. It looks hard to understand right now.

    Why can't you access the website i gave you ? Are you using IE6 ? Or you have javascript turned off ? That site doesn't support IE6 and it does support javascript ON.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the web site was down earlier today, but it is available now

    it does not answer my question about why 1259730015_thumb.jpg instead of 1259730046_thumb.jpg
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because its first row, it means its uploaded first, its the main image. Make sense ?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    unfortunately you don't seem to have a timestamp on the row, so we can't use that

    we can't even use an auto_increment (not generally a good idea, but it would do in a pinch)

    so you're stuck using an existing column, e.g. MIN(thumb_name)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •