How to select two records from each table

[memtimintursun]

How to select two records from each table?

just say example;

I have two tables, one is category and another one is product. product table has categoryid joined to category table on categoryid. I would like select only two records from each table.

example; if there were 5 category, then the result will be 10. if there were 12 category, then the result will be 24. but should be limit it 20 records only, two records from each table.

thanks!

[r937]

which 2? the most expensive 2 products? the 2 products with the shortest names? the last 2 products added?

[memtimintursun]

First of all, thanks for the reply r937.

last added two records from each category. which means, last two records from every cat.

thanks!

[r937]

okay, i can do that

would you kindly do a SHOW CREATE TABLE for both tables please

[memtimintursun]

This is just example codes and I need to select two records from each Category.

Thanks!

SELECT parentcategory.categoryname as parentcategoryname, Productcategory.categoryname as categoryname, Productcategory.parentcategoryid, Product.* from (Productcategory right join Product on Productcategory.categoryid = Product.categoryid) left join Productcategory as parentcategory on Productcategory.parentcategoryid = parentcategory.categoryid  WHERE Product.activated = 'True' order by Product.DateAdded desc LIMIT 20;

CREATE TABLE `Product` (
  `CategoryID` int(11) NOT NULL default '1',
  `ItemID` varchar(30) collate utf8_unicode_ci NOT NULL,
  `Name` varchar(255) collate utf8_unicode_ci default NULL,
  `DateAdded` datetime default NULL,
  PRIMARY KEY  (`InternalID`,`ItemID`),
  UNIQUE KEY `ItemID` (`ItemID`),
  KEY `CategoryID` (`CategoryID`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `Category` (
  `ParentCategoryID` int(11) default '0',
  `CategoryID` int(11) NOT NULL auto_increment,
  `CategoryName` varchar(60) collate utf8_unicode_ci default NULL,
  `CategoryLabel` varchar(150) collate utf8_unicode_ci default NULL,
  `CategoryDesc` varchar(255) collate utf8_unicode_ci default NULL,
  `CategoryImageFile` varchar(50) collate utf8_unicode_ci default NULL,
  `CategoryActivate` varchar(5) collate utf8_unicode_ci default 'YES',
  `CategorySortOrder` int(11) default NULL,
  PRIMARY KEY  (`CategoryID`),
  UNIQUE KEY `CategoryID` (`CategoryID`),
  KEY `ParentCategoryID` (`ParentCategoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

[r937]

shirley...

... you do not write your SQL all on one humoungously long line like that??!!!

[r937]

if the primary key of Category is CategoryID, then this --

  UNIQUE KEY `CategoryID` (`CategoryID`)

is totally redundant and inefficient

how come your SHOW CREATE TABLE shows Category as the table name, but in your query you use Productcategory?

[r937]

your Product table has a primary key which includes a column that isn't in the table!!!

sorry for all the comments, but i'm trying to understand your tables and it is becoming apparent that there has been some monkey business

[memtimintursun]

first of all, thanks for that suggestion about the UNIQUE KEY.

well, actually this is just example, not real code and I have just type it here by mistakes.

you can just rename it and try.

please help me, Thanks!

[r937]

SELECT parentcategory.categoryname as parentcategoryname
     , category.categoryname
     , product.itemid
     , product.name
     , product.dateadded 
  FROM category 
LEFT OUTER
  JOIN category AS parentcategory
    ON parentcategory.categoryid = category.parentcategoryid
INNER
  JOIN product 
    on product.categoryid = category.categoryid
 WHERE product.activated = 'True' 
   AND ( SELECT COUNT(*)
           FROM product AS others
          WHERE others.categoryid = product.categoryid
            AND others.dateadded > product.dateadded ) < 2
ORDER 
    BY parentcategory.categoryname
     , category.categoryname
     , product.dateadded DESC

alternatively, if you like scrolling...

SELECT parentcategory.categoryname as parentcategoryname , category.categoryname , product.itemid , product.name , product.dateadded FROM category LEFT OUTER JOIN category AS parentcategory ON parentcategory.categoryid = category.parentcategoryid INNER JOIN product on product.categoryid = category.categoryid WHERE product.activated = 'True' AND ( SELECT COUNT(*) FROM product AS others WHERE others.categoryid = product.categoryid AND others.dateadded > product.dateadded ) < 2 ORDER BY parentcategory.categoryname , category.categoryname , product.dateadded DESC

[memtimintursun]

dear r937,

it doesn't work and I am trying it since two hours.. it doesn't show any error message and 20 minutes waiting for the result, but there are no results and I just hear voice CPU works hard, but nothing happened.

on database more than 1000 records on news table and 8 records on category table. and it should work maximum in just few second time to get the results, but doesn't.

now I am putting here all code about news website, can you take a look please?

I just need two records from every single category in this query

SELECT parentcategory.categoryname as parentcategoryname, newscategory.categoryname as categoryname, newscategory.parentcategoryid, news.* from (newscategory right join news on newscategory.categoryid = news.categoryid) left join newscategory as parentcategory on newscategory.parentcategoryid = parentcategory.categoryid  WHERE news.activated = 'True' order by news.newsdate desc LIMIT 20;

DROP TABLE IF EXISTS `news`;
CREATE TABLE "news" (
  "CategoryID" int(11) NOT NULL,
  "InternalID" int(11) NOT NULL,
  "ItemID" varchar(30) collate utf8_unicode_ci NOT NULL,
  "Name" varchar(255) collate utf8_unicode_ci default NULL,
  "Memo" longtext collate utf8_unicode_ci,
  "Picture" varchar(255) collate utf8_unicode_ci default NULL,
  "Activated" varchar(5) collate utf8_unicode_ci default NULL,
  "DateAdded" datetime default NULL,
  "ExpiryDate" datetime default NULL,
  "SortOrder" int(11) default NULL,
  "Readed" int(11) default NULL,
  "AdminNote" mediumtext collate utf8_unicode_ci,
  PRIMARY KEY  ("ItemID"),
  UNIQUE KEY "InternalID" ("InternalID"),
  KEY "CategoryID" ("CategoryID"),
  CONSTRAINT "CategoryFK" FOREIGN KEY ("CategoryID") REFERENCES "newscategory" ("CategoryID") ON UPDATE CASCADE
);

-- ----------------------------
-- Table structure for newscat
-- ----------------------------
DROP TABLE IF EXISTS `newscategory`;
CREATE TABLE "newscategory" (
  "ParentCategoryID" int(11) default NULL,
  "CategoryID" int(11) NOT NULL,
  "CategoryValue" varchar(50) collate utf8_unicode_ci default NULL,
  "CategoryDesc" varchar(255) collate utf8_unicode_ci default NULL,
  "CategoryLabel" varchar(50) collate utf8_unicode_ci default NULL,
  "CategoryImageFile" varchar(50) collate utf8_unicode_ci default NULL,
  "Activated" varchar(5) collate utf8_unicode_ci default NULL,
  "SortOrder" int(11) default NULL,
  PRIMARY KEY  ("CategoryID"),
  UNIQUE KEY "CategoryID" ("CategoryID"),
  KEY "ParentCategoryID" ("ParentCategoryID")
);

[r937]

i tested my query on your "revised" table definitions, and it works fine