A simple mySQL statement to ask

1
votes

I've a question to ask about mysql, I am creating a supplier page which requires list down all the suppliers details and their products (randomly choose 3 product for each supplier), so we have to join 2 tables together, one is suppliers, another one is products, may I know how to present them in single mysql statement?

table1 - suppliers

id      corp_name      product_count
---------------------------------------
AD0728   John Smith     187
AD0729   JayJay         1983

table2 - products

pid      cid        p_name      quantity
---------------------------------------
1      AD0728     Body Shampoo     10
2      AD0728     glass            10
3      AD0728     pen              10
4      AD0728     pencil           10
5      AD0728     eraser           10
6      AD0728     speaker          10
7      AD0729     monitor          10
8      AD0729     keyboard         10
9      AD0729     mouse            10
10     AD0729     processor        10
11     AD0729     box              10
12     AD0729     sunglass         10

Output

id      corp_name      pid          p_name          quantity
----------------------------------------------------------------
AD0728   John Smith     1           Body Shampoo       10
AD0728   John Smith     2           glass              10
AD0728   John Smith     3           pen                10
AD0729   JayJay         10          processor          10
AD0729   JayJay         11          box                10
AD0729   JayJay         12          sunglass           10

Thanks everyone.

3
phpmysql
What RDBMS? SQL Server? MySQL? Oracle? That will make a difference - LittleBobbyTables - Au Revoir
please show us what you have tried so far? show us your code, then we might help you. - Fahim Parkar
For SQL Server 2005 or greater, see: stackoverflow.com/questions/3998573/… - LittleBobbyTables - Au Revoir
Duplicate of stackoverflow.com/questions/3140589/… - Faruk Sahin
Sorry all, I make a mistake, it is mysql, not SQL, so sorry to make this mistake... Hi Fahim Parkar, I don't know how to write this statement, sorry - 風吟月

3 Answers

6
votes

For SQL Server 2005 or greater you can use ORDER BY NEWID() for a random order and you can use ROW_NUMBER to get your partion:

WITH CTE AS
(
   SELECT 
       s.id, s.corp_name, p.pid, p.p_name, p.quantity
     , RN = ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY NEWID())
    FROM suppliers s
    INNER JOIN products p ON s.id = p.cid
)
SELECT id, corp_name, pid, p_name, quantity
FROM CTE WHERE RN <= 3

Here's the fiddle (thanks to @MahmoudGamal): http://www.sqlfiddle.com/#!3/a26c6/17/0

1
votes

Check this SQL Fiddle code. It will return as requested the first 3 products for each supplier. It will work for SQL2005 and greater as it uses CTE (Common Table Expressions), introduced with SQL2005.

Either ways, here is the code:

;WITH myCTE AS (
 SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.cid ORDER BY NEWID()) AS r
   FROM products p)
SELECT sup.id,sup.corp_name,c.pid,c.p_name,c.quantity
  FROM myCTE c JOIN suppliers sup ON c.cid = sup.id
 WHERE c.r <= 3
1
votes

In MYSQL this works: http://sqlfiddle.com/#!2/8b291/10

SET @rank=0;
SELECT *, @rank:=@rank+1 AS rank
FROM suppliers s
LEFT JOIN products p ON p.cid = s.id
GROUP BY s.id, @rank % 3