CODEWITHSUNDEEP
mysqlsql
Wracker
Wracker

Reputation: 619

mysql combining data from two tables

Not sure if this is possible on DB side, but so far I can only get this result:

Query: 

SELECT City.city_name "City", PC.subcategory_id "Subcategory", PC.count "count" FROM products_counter PC , Cities City WHERE PC.city_id = City.city_id

+-----------+----------------+-------+
| city_name | subcategory_id | count |
+----------------------------+-------+
| City1     | fruits         | 4     |
| City2     | vegetables     | 4     |
| City1     | meat           | 1     |
+-----------+----------------+-------+

Here are my two tables :

Table products_counter:

+-------+---------+----------------+-------+
| ID    | city_id | subcategory_id | count |
+-------+---------+----------------+-------+
|     1 |       1 | fruits         |     4 |
|     2 |       2 | vegetables     |     4 |
|     3 |       2 | meat           |     1 |
+-------+---------+----------------+-------+

Table cities:

+---------+------------+
| city_id | city_name  |
+---------+------------+
|       1 | City1      |
|       2 | City2      |
|       3 | City3      |
+---------+------------+

and this is the expected result:

+-----------+----------------+-------+
| city_name | subcategory_id | count |
+-----------+----------------+-------+
| City1     | fruits         | 4     |
| City1     | vegetables     | 0     | 
| City1     | meat           | 0     |
| City2     | fruits         | 0     |
| City2     | vegetables     | 4     |
| City1     | meat           | 1     |
| City3     | fruits         | 0     |
| City3     | vegetables     | 0     |
| City3     | meat           | 0     |
+-----------+----------------+-------+

But I'm not really sure how to list all the cities from Cities table and then just assign the count column if the city_id and subcategory_id are equal.

Upvotes: 0

Views: 62

Answers (3)

Rakesh Soni
Rakesh Soni

Reputation: 10907

select city_name, subcategory_id,
case when cities.city_id = pc.city_id then `count` else 0 end as counter
from cities, products_counter pc

Upvotes: 1

Nilesh Thakkar
Nilesh Thakkar

Reputation: 1459

You can use cross join for this.

SELECT c.city_name, pc.subcategory_id,
IFNULL((select `count` from products_counter where city_id = c.city_id 
                and subcategory_id = pc.subcategory_id),0) as 'Count'
FROM cities c CROSS JOIN products_counter pc

working example here - http://sqlfiddle.com/#!9/34c38/16

Upvotes: 1

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Try this.

select  c.city_name  ,
        ps.subcategory_id ,
        (   select  `count` 
            from    products_counter 
            where   city_id = c.city_id 
                and subcategory_id = ps.subcategory_id) as 'Count'
from       cities           c
cross join products_counter pc

Upvotes: 1

Related Questions