How to Display Join table from another table?

SpawnSheep 0
Viewed 25

I have problem with querying data in MySQL, I want my data to display like this:

id Name Kp price
KI014 TPD SS10 1000
SS11 2000
SS12 3000
KI015 ASD SK14 1500
SK15 2500

My query is like this:

SELECT SQL_CALC_FOUND_ROWS produkreseller.IDRESELLER as ID,
       masterreseller.NAMARESELLER as Name, 
       produk.KodeProduk as KP, 
       produkreseller.hargajual as Price 
FROM produkreseller 
INNER JOIN produk ON produkreseller.IdProduk = produk.IDPRODUK 
INNER JOIN masterreseller ON produkreseller.IDRESELLER = masterreseller.idreseller
ORDER BY masterreseller.idreseller;

And the result from that query is:

id Name Kp price
KI014 TPD SS10 1000
KI014 TPD SS11 2000
KI014 TPD SS12 3000
KI015 ASD SK14 1500
KI015 ASD SK15 2500
mysql sql
1 Answers

The basic idea is to partition the data by IDRESELLER, check if the current record is the first record in that partition, and if so, include the value of IDRESELLER (or NAMARESELLER) in the result, otherwise include the empty string.

SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY produkreseller.IDRESELLER) = 1 
            THEN produkreseller.IDRESELLER
            ELSE '' END as ID,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY produkreseller.IDRESELLER) = 1 
            THEN masterreseller.NAMARESELLER
            ELSE '' END as Name,
       produk.KodeProduk as KP, 
       produkreseller.hargajual as Price 
  FROM produkreseller 
 INNER JOIN produk ON produkreseller.IdProduk = produk.IDPRODUK 
 INNER JOIN masterreseller ON produkreseller.IDRESELLER = masterreseller.idreseller
 ORDER BY id;

This solution makes use of window function ROW_NUMBER(). More information about window functions can be found in the documentation.

Update

In MySQL 5 there are no window functions but one can make use of variables:

SELECT CASE WHEN ID <> @last_id THEN ID ELSE '' END as ID,
       CASE WHEN ID <> @last_id THEN Name ELSE '' END as Name,
       KP, Price,
       @last_id := ID
  FROM (SELECT produkreseller.IDRESELLER as ID,
               masterreseller.NAMARESELLER as Name,
               produk.KodeProduk as KP,
               produkreseller.hargajul as Price
          FROM produkreseller
         INNER JOIN produk ON produkreseller.IdProduk = produk.IDPRODUK
         INNER JOIN masterreseller ON produkreseller.IDRESELLER = masterreseller.idreseller
         ORDER BY id) products, (SELECT @last_id := '') x;

As I don't have a possibility to run those queries, consider them as untested sketches (that is they may contain errors).

Mihe0
Related