SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    SitePoint Member
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    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!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    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;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    shirley...

    ... you do not write your SQL all on one humoungously long line like that??!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if the primary key of Category is CategoryID, then this --
    Code:
      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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    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!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    alternatively, if you like scrolling...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    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")
    );

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i tested my query on your "revised" table definitions, and it works fine
    rudy.ca | @rudydotca
    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
  •