CODEWITHSUNDEEP
sqlite
Andy Band
Andy Band

Reputation: 313

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       |  data_out        |   data_in |
    +--------+------------+------------------+-----------+
    | 1      | Produ1     | null             | 2015-01-06|
    | 2      | Produ1     | null             | 2015-01-06|
    | 3      | Produ3     | null             | 2015-01-06|
    | 4      | Produ3     | null             | 2015-01-06|
    | 5      | Produ5     | null             | 2015-01-06|
    | 6      | Produ4     | 2015-01-06       | 2015-02-06|
    | 7      | Produ2     | 2015-01-06       | 2015-02-06|
    +--------+------------+------------------+-----------+

table: tb_product

+--------+------------+
| item_id| nome       |
+--------+------------+
| 1      | Produ1     |
| 2      | Produ2     |
| 3      | Produ3     |
| 4      | Produ4     |
| 5      | 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?

EDIT

I have create a new query like this:

    SELECT DISTINCT nome, pezzi FROM(
SELECT nome,COALESCE(pezzi,0)as pezzi FROM(
SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.nome = s.nome
WHERE s.data_out is null
OR    s.data_out = ""
GROUP BY p.nome
union
select nome,null as pezzi from tb_product) )
ORDER BY pezzi DESC

but i have some duplicate item to remove....the result is

nome         pezzi
crema         2
pistacchio    2
zabajone      2
bacio         1
cassata       1
cioccolato    1
ciocco rum    1
malaga        1
mango         1
mascarpone    1
nocciola      1
stracciatella 1
bacio         0
caramello     0
cassata       0
cioccolato    0
ciocco rum    0
crema         0
fragola       0
limone        0
malaga        0
mango         0
mascarpone    0
nocciola      0
pistacchio    0
zabajone      0

is possible to remove the duplicate that are 0?

SELECT DISTINCT nome, pezzi FROM(
SELECT nome,COALESCE(pezzi,0)as pezzi FROM(
SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.nome = s.nome
WHERE s.data_out is null
OR    s.data_out = ""
GROUP BY p.nome
union
select nome,null as pezzi from tb_product) GROUP BY nome ) 
ORDER BY pezzi DESC

Upvotes: 1

Views: 87

Answers (1)

SMA
SMA

Reputation: 37023

You need to do a left join between the two tables like:

SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.name = s.name
WHERE data_out is null
OR    data_out = ""
GROUP BY p.nome
ORDER BY pezzi DESC

Upvotes: 1

Related Questions