CODEWITHSUNDEEP
mysql
Irsyad Nurilhaq
Irsyad Nurilhaq

Reputation: 15

Mysql : Join 4 table problem with the result

I have 4 table and join with all then i want show data like here :

pr_id |nama | jumlah_liker | id_user
1     |Milk |        5     | 1
2     |Choco|        0     | 1

Review Table produk

pr_id | nama
1     | Milk
2     | Choco
3     | Salad

Review Table liker

id_produk | id_user
1         | 1 
1         | 1 
1         | 1 
1         | 3 
1         | 2

Review Table featured_ukm

id | id_produk
1  | 1 
2  | 2

But i got sql record 

id_produk | nama  | jumlah_liker | id_user
1         | milk  | 1            | 1  //problem row i dont get count all record id_produk
2         | choco | 0            | 1

    SELECT produk.*, COUNT(liker.id_produk), liker.id_user
FROM produk 
    left join liker ON liker.id_produk = produk.pr_id AND liker.id_user = 1
    INNER JOIN featured_ukm ON featured_ukm.id_produk = produk.pr_id
    GROUP BY featured_ukm.id_produk

Upvotes: 0

Views: 29

Answers (1)

Justin Jack
Justin Jack

Reputation: 535

  • Edit: My suggestions are first, I actually put what will produce your exact requested results at the bottom

It appears that you're trying to show how many likes each of the "Featured Product" has.

It isn't entirely clear what you're trying to do with the user id, or what its purpose in the result set is; here is a query to show how many likes each "Featured Product" has:

SELECT 
produk.*, 
(SELECT IFNULL(COUNT(*), 0)
FROM liker WHERE liker.id_produk = produk.pr_id)
FROM featured_ukm F
INNER JOIN produk ON produk.pr_id = F.id_produk

Here is a query to show how many likes each product by each user

SELECT DISTINCT 
P.*, 
(SELECT IFNULL(COUNT(*), 0) FROM liker WHERE liker.id_user = L.id_user), 
IFNULL(L.id_user, 0)
FROM produk P
LEFT JOIN liker L ON L.id_produk = P.pr_id

and if you want to just see the items that ONE user liked, add:

WHERE L.id_user = 1

to the end of it.

Here is a query to show how many likes each Featured Product has for each user id:

SELECT DISTINCT 
P.*, 
(SELECT IFNULL(COUNT(*), 0) FROM liker WHERE liker.id_user = L.id_user), 
IFNULL(L.id_user, 0)
FROM produk P
LEFT JOIN liker L ON L.id_produk = P.pr_id
INNER JOIN featured_ukm F on F.id_produk = P.pr_id

To get the exact result set that it would appear that you're looking for (in your example), you will need to reference a user table in your query. I assume you have one because you're referencing user ids. For the purpose of this example, I'm creating the below user table.

id  |   name
----------------
 1  | user_one
 2  | user_two
 3  | user_three

With this table, the following query will give exactly what it appears you're looking for:

SELECT P.*, (SELECT IFNULL(COUNT(*), 0) FROM liker WHERE liker.id_produk = 
F.id_produk), user.id
FROM user
CROSS JOIN featured_ukm F
LEFT JOIN produk P ON F.id_produk = P.pr_id
WHERE user.id = 1

But play around with some of my other example queries. They may be more helpful.

Upvotes: 1

Related Questions