dimanche 31 mai 2015

SQLite compare query from 2 tables

I would like to create a new query for know how many product are in the store.

table: tb_store

+--------+------------+------------------+-----------+
| item_id| nome       |  date_out        |   date_in |
+--------+------------+------------------+-----------+
| 1      | Produ1     | null             | 2015-01-06|
| 2      | Produ1     | null             | 2015-01-06|
| 3      | Produ3     | null             | 2015-01-06|
| 3      | Produ3     | null             | 2015-01-06|
| 3      | Produ5     | null             | 2015-01-06|
+--------+------------+------------------+-----------+

table: tb_product

+--------+------------+
| item_id| nome       |
+--------+------------+
| 1      | Produ1     |
| 2      | Produ2     |
| 3      | Produ3     |
| 3      | Produ4     |
| 3      | Produ5     |
+--------+------------+

i have write this query:

select nome, count(nome) as pezzi from  tb_store where data_out is null or data_out="" group by nome order by pezzi desc

the result are:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
+--------+------------+

i would like to obtain this result:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
| Produ2 | 0          |
| Produ4 | 0          |
+--------+------------+

is possible? how can rewrite the query?

Aucun commentaire:

Enregistrer un commentaire