1

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.

風吟月
  • 243
  • 1
  • 3
  • 10

3 Answers3

6

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

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I think you have your table prefixes and field names screwed up a bit. This should work: 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 – Tom Nov 12 '12 at 14:16
  • Check your join, I think the field names are still reversed. s/b: INNER JOIN products p ON s.id = p.cid – Tom Nov 12 '12 at 14:22
  • @Tim Schmelter: thanks for your kindly help, but do you have this in mysql statement? – 風吟月 Nov 12 '12 at 14:25
  • Please tag your rdbms next time in the first place. Have a look at Varon's answer. – Tim Schmelter Nov 12 '12 at 14:28
1

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
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
1

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
Varon
  • 3,736
  • 21
  • 21
  • thanks Varon, I'd tried to run it in phpmyadmin, but it seems not working....do you know why? – 風吟月 Nov 12 '12 at 15:34
  • In my own phpmyadmin it works too. Perhaps do you have an old version of mysql-server or phpmyadmin? Works `SET @a = 1; SELECT @a;` ? – Varon Nov 12 '12 at 15:45
  • the error is # MySQL returned an empty result set (i.e. zero rows). – 風吟月 Nov 12 '12 at 15:59
  • the result is just workable on group by part, simply speaking, what I want is 3 product for each supplier, but the result is just list 1 product for each supplier, my phpmyadmin is 2.11.2.1, is it old version? – 風吟月 Nov 12 '12 at 16:01
  • yes. this is old. here an online phpmyadmin for testing: http://demo.phpmyadmin.net/STABLE/?pma_username=root . click SQL and write in query: `use ranktest; SET @rank=0; SELECT *, @rank:= ...` – Varon Nov 12 '12 at 16:14
  • thanks Varon, I go and update now. one more question to ask, how to put the above coding into php code? is it like this? $sql = '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';$result = mysql_query($sql) or die("Query failed"); – 風吟月 Nov 12 '12 at 16:22
  • Thanks for your help, and nice to meet you Varon:) – 風吟月 Nov 12 '12 at 18:28