how to limit in left job

[runrunforest]

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 ?

[vigneshr35]

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,

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

[vigneshr35]

Sorry , ignore the previous Query,

Try this,...This should work,

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

[r937]

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

[runrunforest]

Yes, R937 understands me right. Can you suggest solution?

[r937]

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

[runrunforest]

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 ;

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.

[r937]

... 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...

[runrunforest]

Sorry for my bad english. let me show my data, that would make sense ?

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

[r937]

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...

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

[runrunforest]

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 ?

[r937]

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

[runrunforest]

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.

[r937]

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

[runrunforest]

because its first row, it means its uploaded first, its the main image. Make sense ?

[r937]

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)