SitePoint Sponsor |
|
User Tag List
Results 1 to 12 of 12
-
Oct 9, 2009, 10:14 #1
- Join Date
- Sep 2007
- Posts
- 19
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How to select two records from each table
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!
-
Oct 9, 2009, 11:27 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
which 2? the most expensive 2 products? the 2 products with the shortest names? the last 2 products added?
-
Oct 9, 2009, 13:26 #3
- Join Date
- Sep 2007
- Posts
- 19
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
First of all, thanks for the reply r937.
last added two records from each category. which means, last two records from every cat.
thanks!
-
Oct 9, 2009, 14:08 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
okay, i can do that
would you kindly do a SHOW CREATE TABLE for both tables please
-
Oct 9, 2009, 14:53 #5
- Join Date
- Sep 2007
- Posts
- 19
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
This is just example codes and I need to select two records from each Category.
Thanks!
Code: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;
Code: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;
Code: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;
-
Oct 9, 2009, 15:03 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
shirley...
... you do not write your SQL all on one humoungously long line like that??!!!
-
Oct 9, 2009, 15:06 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
if the primary key of Category is CategoryID, then this --
Code:UNIQUE KEY `CategoryID` (`CategoryID`)
how come your SHOW CREATE TABLE shows Category as the table name, but in your query you use Productcategory?
-
Oct 9, 2009, 15:09 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Oct 9, 2009, 15:14 #9
- Join Date
- Sep 2007
- Posts
- 19
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Oct 9, 2009, 15:15 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code: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
Code: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
-
Oct 9, 2009, 21:29 #11
- Join Date
- Sep 2007
- Posts
- 19
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Code: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;
Code: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") );
-
Oct 10, 2009, 04:28 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i tested my query on your "revised" table definitions, and it works fine
Bookmarks