Query Top 5 item related

Elstefen · #1 (**permalink**) 06-07-09, 08:55

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?

r937 · #2 (**permalink**) 06-07-09, 10:07

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)

Elstefen · #3 (**permalink**) 06-08-09, 07:22

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.