Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Query Top 5 item related

    Hi,
    i'm implementing a shop.
    i want to extract the top 5 items related with the item in input.
    the idea is to check inside the bought item to findout the order where the input item appears. When i've these order i count the items and extract the top5.
    Code:
    CREATE TABLE item(
    	idItem integer,
    	name varchar,
    	description varchar,
    	price float,
    	image varchar,
    	idCategory integer,
    	stock integer,
    	foreign key (idCategory) references category(idCategory),
    	primary key (idItem)
    );
    
    
    CREATE TABLE orders(
    	idOrder integer,
    	idCustomer integer,
    		dateTime timestamp default now(),
    		foreign key (idCustomer) references customer(idCustomer),
    	primary key(idOrder)
    );
    
    CREATE TABLE bought(
    idBought integer,
    idOrder integer,
    idItem integer ,
    quantity integer,
    foreign key (idOrder) references orders(idOrder),
    foreign key (idItem) references item(idItem),
    primary key(idBought)
    );
    i've done this query:

    Code:
    select idItem, count(idItem) as count from bought where idOrder in (select idorder from bought where idItem = 1) and idItem<>1 group by iditem order by count DESC
    but, how to extract the item? i've the idItem and a counter, how can i have the item table?

    there's a better way to write down the same query?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT item.idItem
         , item.name
         , item.description
         , data.howmany
      FROM (
           SELECT idItem
                , COUNT(*) AS howmany 
             FROM ( SELECT idorder 
                      FROM bought 
                     WHERE idItem = 1 ) AS these
           INNER
             JOIN bought 
               ON bought.idOrder = these.idOrder 
              AND bought.idItem <> 1 
           GROUP 
               BY iditem 
           ) AS data
    ORDER 
        BY data.howmany DESC LIMIT 5
    note that LIMIT 5 gives you your top 5 in mysql, but it is ~not~ ANSI SQL (which is the forum you posted in)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    but how can i extract the entire table of item without the counter?

    Code:
    SELECT item.*
      FROM (
           SELECT idItem
                , COUNT(*) AS howmany 
             FROM ( SELECT idorder 
                      FROM bought 
                     WHERE idItem = 1 ) AS these
           INNER
             JOIN bought 
               ON bought.idOrder = these.idOrder 
              AND bought.idItem <> 1 
           GROUP 
               BY iditem 
           ORDER 
             BY data.howmany DESC LIMIT 5
           )
    this can be work?

    the top 5 because i used postgres and has this feature. i know that is not possible in sql ansi.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Elstefen
    this can be work?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •