CODEWITHSUNDEEP
mysqlsql
Tushar
Tushar

Reputation: 166

MySQL SELECT Count statement issue

I have two MySQL queries:

First: 

SELECT DISTINCT (broker.broker_id),company_id ,broker_name,company_name,mobile1_no,email,pan_card_num,broker_id,broker_id,company_id
FROM broker_firm AS broker_firm
LEFT JOIN broker ON broker_firm.company_id = broker.firm_id
AND broker_firm.is_active =1
AND broker.is_active =1

This query is generating 331 results.

Second:

SELECT COUNT( broker.broker_id ) AS tot
FROM broker_firm AS broker_firm
LEFT JOIN broker AS broker ON broker_firm.company_id = broker.firm_id
AND broker_firm.is_active =1
AND broker.is_active =1

This query is generating 289 results.

Can anyone please tell me the reason why? I expected both of the results to be same. Or maybe, the Count(*) result to be greater.

Thanks in advance

Upvotes: 0

Views: 88

Answers (3)

Abylay Sabirgaliyev
Abylay Sabirgaliyev

Reputation: 726

First query counts all firms, you have 42 firms with no broker. try

select count(broker_firm.company_id)
FROM broker_firm
where broker_firm.company_id not in (select firm_id from broker)

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

why dont you use both in one query ?

 SELECT broker.broker_id,company_id ,broker_name,company_name,mobile1_no,email,pan_card_num,broker_id,broker_id,company_id,COUNT( broker.broker_id ) AS tot
 FROM broker_firm AS broker_firm
 LEFT JOIN broker ON broker_firm.company_id = broker.firm_id
 AND broker_firm.is_active =1
 AND broker.is_active =1
 GROUP BY broker.broker_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

When you do a left join, the logic is simple: keep all the rows in the first table, regardless of whether the condition in the on clause is true. If the condition is false, then all the columns in the second table get a value of NULL.

When you do an inner join, the logic is to keep all rows in the first table.

In the first query, the additional conditions are in the on clause. Hence, all rows in the first table are kept (and don't forget that the join itself may result in duplicates). In the second query, the where clause has a condition broker.is_active = 1. This condition will fail when is_active is NULL -- which is what happens when the records don't match. In other words, the condition is turning the left join into an inner join.

EDIT:

The idea is the same. The second query is counting the matching records. count(broker.broker_id) counts the non-NULL values for that column. This is the same as doing an inner join.

The first query is counting all records. select distinct selects distinct values of all the columns. Your syntax is a bit confusing, because it suggests that you just want the distinct value of one column. But that is not how it works. Because you have columns from both tables in the select, the non-matching brokers will have their company information on the row, making that row distinct from all other rows.

Upvotes: 1

Related Questions