how to get total records from left table and matched records form right table in mysql

Question

i have 2 table. how to get total records from left table and matched records form right table in mysql? i am using below query but it will get only matched records from two tables.

SELECT post_id,COUNT(post_id) as pid,hostel_id,ht.user_id,hostel_name,
       hostel_type,hostel_district,hostel_area,post_date,hostel_rent,hostel_respond,
       h_contact_num,created_date,h_food_type
FROM hostels ht
left join histroy hr
    ON ht.hostel_id =hr.post_id
WHERE ht.hostel_district=$city_code AND
      ht.status='1' AND
      hr.post_type='Hostel'
GROUP BY hr.post_id
ORDER by pid DESC

Can you show some sample data to illustrate what you want here? Your ask is unclear to me in the context of a `GROUP BY` query. — Tim Biegeleisen, Jan 30 '17 at 10:07
You have to add all columns except `COUNT(post_id) as pid` in your group by clause. — Furqan Aziz, Jan 30 '17 at 10:09
Maybe [this question](http://stackoverflow.com/questions/3453809/how-to-use-mysql-found-rows-in-php) will help you — Bojan Radaković, Jan 30 '17 at 10:12

score 1 · Answer 1 · answered Jan 30 '17 at 10:12

1

If you want the number of total records per group, then just COUNT(*). If you want the number of records from the histroy table which matched to something in the hostels table, then your current use of COUNT(post_id) should already be doing this. If you want the number of records in hostels which did not match to anything in histroy, then you can use this:

SUM(CASE WHEN post_id IS NULL THEN 1 ELSE 0 END) AS num_hostels_no_match

answered Jan 30 '17 at 10:12

Tim Biegeleisen

502,043
27
286
360

Is that the same as "doesn't match anything" or is that just "doesn't match something" – Strawberry Jan 30 '17 at 11:31

score 0 · Answer 2 · answered Jan 30 '17 at 10:39

Not really a solution more an illustrated comment. A left (outer) join is the correct way to get everything from hostels but aggregate functions ignore null values. for example given

drop table if exists t;

create table t (id int, val int);
insert into t values
(1,1),(2,null),(3,3);

this query

MariaDB [sandbox]> select count(val) nonnullcount
    -> from t;

returns

+--------------+
| nonnullcount |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

If you want the count to include all entries then you should cater for the null values.

how to get total records from left table and matched records form right table in mysql

2 Answers2