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