dimanche 8 mai 2016

SQLite LEFT JOIN Usage is Not Working Correctly

I have 3 TABLES. Costumer, Product and Stats. When user select a user and a product, i increase Stats->count by 1. Stats table has id, cid, pid and count values. My purpose is sort products by count and they should be alphabetic. This is my sql request;

SELECT * FROM product AS p
         LEFT JOIN stat AS s
         ON p._id = s.stats_product_id
         WHERE s.stats_customer_id = 'customerId'
         ORDER BY s.stats_count desc , p.product_name COLLATE NOCASE;

The problem is; because i create Stats values after i create some order, at the beginning i can't see all of products. Because some of products that i've never used are not in my Stats table. I know that LEFT JOIN should give me all of products values but it doesn't... Please help me. Thanks in advice.

Ex: I have a,b,c,d,e,f products. Let's think that a,b bought 3 times (their counts are 3), e,f bought 2 times (their counts are 2) and c,d never bought (they are not in stats table because they never bought by anyone and that's the problem tho!). Output order should be: a b e f c d HANDLED: Solution on answer #1.

Aucun commentaire:

Enregistrer un commentaire