CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user9282535
user9282535

Reputation:

MySql query to fetch results from two tables

I want to fetch total retailers from retailer table and other attributes from visit info table. This query is giving correct result for total retailers and visited retailers but giving an incorrect result for feedback count, promise order count etc two columns.

select count(distinctrow tbl_retailer.retailer_id) as total_retailers,count(distinctrow tbl_visit_info.retailer_id) as visited_retailers,
SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
from tbl_visit_info,tbl_retailer 
where visitor_id=175 and tbl_retailer.sr_id=175;

This is my retailer table from which I want to count total retailers from this table

This is my visit_info table from which I want to count visited_retailers, feedback count, Promise_order_count_stock_count and payment count which are defined as in activity_type column in visit_info table

Upvotes: 0

Views: 59

Answers (1)

jarlh
jarlh

Reputation: 44786

Have two sub-queries, one for each table. JOIN the results. 

select * from

(select sr_id, count(distinctrow retailer_id) as total_retailers
 from tbl_retailer 
 group by sr_id) tr

join

(select visitor_id,
        count(distinctrow retailer_id) as visited_retailers,
        SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
        SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
        SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
        SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
 from tbl_visit_info
 group by visitor_id) tvi

on tvi.visitor_id = tr.sr_id
where tvi.visitor_id = 175

Simply remove the WHERE clause to get info about all visitors!

BTW, distinctrow is probably not needed here.

Upvotes: 1

Related Questions