mysql subquery in where in clause

Question

I execute Query A, it gives me the result i.e 4,5,6. And If I use that result directly into the Query B in WHERE IN clause, it gives me the perfect result again. But If I use both the query combined in Query C, it is not generating any answer.

Query A

SELECT `region`.`district_id` 
FROM `region` 
LEFT JOIN `regional_owner` ON `regional_owner`.`r_id` = `region`.`id`
WHERE `regional_owner`.`email_id` = 'balajibarhate10@gmail.com';

Produces O/P - 4,5,6 which I use below

Query B

SELECT sum(`purchase`.`purchase_ammount`), `franchise`.`district_id`
FROM `purchase`
LEFT JOIN `franchise` ON `franchise`.`id` = `purchase`.`f_id`
WHERE `franchise`.`district_id` in ( 
         4,5,6
    )
    AND purchase.purchase_date BETWEEN Date_format(CURDATE(), "%Y-%m-01") AND CURDATE()
GROUP BY `franchise`.`district_id`

Now, I am combining both of the above queries in one as below

Query C

SELECT sum(`purchase`.`purchase_ammount`), `franchise`.`district_id`
FROM `purchase`
LEFT JOIN `franchise` ON `franchise`.`id` = `purchase`.`f_id`
WHERE `franchise`.`district_id` in ( 
    SELECT `region`.`district_id` 
    FROM `region` 
    LEFT JOIN `regional_owner` ON `regional_owner`.`r_id` = `region`.`id`
    WHERE `regional_owner`.`email_id` = 'balajibarhate10@gmail.com'
)

AND purchase.purchase_date BETWEEN Date_format(CURDATE(), "%Y-%m-01") AND CURDATE()
GROUP BY `franchise`.`district_id`

I am unable to understand, which part is wrong. Please guide me.

You use an (inner) join in query a but a left join in the sub query in c. — P.Salmon, Jun 23 '20 at 09:40
In query A, you are selecting two columns (`region`.`district_id`). Try selecting only one column (`district_id`) when you use this query as a subquery in query C. — mti2935, Jun 23 '20 at 09:56
@mti2935 Its not two column. Its one table with column name. — user3831445, Jun 23 '20 at 10:09
I see now. I thought it was a `,` but it's a `.`, Time for new glasses. — mti2935, Jun 23 '20 at 10:11
@mti2935, P.Salmon wants to say that, in first query you used JOIN(`from region JOIN regional_owner`) but while combining both queries you used LEFT JOIN(`from region LEFT JOIN regional_owner`). And the default JOIN in mysql is INNER JOIN. See for more [JOIN vs LEFT JOIN](https://stackoverflow.com/questions/9770366/difference-in-mysql-join-vs-left-join) — Dark Knight, Jun 23 '20 at 10:15
@JitendraYadav Sorry for that. I changed the code. It was LEFT JOIN only in the first query. — user3831445, Jun 23 '20 at 10:25
Your query A has a syntax error btw - no WHERE keyword or ON clause. There's no point using a LEFT join because you then mention the left joined table in the WHERE clause, making it an INNER join — Caius Jard, Jun 23 '20 at 10:32
query a and the sub query are not the same - please bring them into line... — P.Salmon, Jun 23 '20 at 10:32
I just found out. Its the issue of typecasting. O/p is coming in string format and I need it in an integer format. How can I do that? — user3831445, Jun 23 '20 at 10:41
What is `o/p` ? To me it means "original poster" - the person who asks a question on SO — Caius Jard, Jun 23 '20 at 10:48

Caius Jard · Answer 1 · 2020-06-23T10:47:44.373

There isn't much point in your left joins; you left join a table but then go and use that table in a WHERE clause, eliminating any nulls introduced by the left join, effectively turning it into an inner join anyway

This is also a simpler way to write your query:

SELECT sum(`purchase`.`purchase_ammount`), `franchise`.`district_id`

FROM `purchase`
JOIN `franchise` ON `franchise`.`id` = `purchase`.`f_id`
JOIN `region` ON `region`.`district_id` = `franchise`.`district_id`
JOIN `regional_owner` ON `regional_owner`.`r_id` = `region`.`id`
    
WHERE
  `purchase`.`purchase_date` BETWEEN date_sub(curdate(),interval DAY(@date)-1 DAY) AND curdate() AND
 `regional_owner`.`email_id` = 'balajibarhate10@gmail.com'
GROUP BY `franchise`.`district_id`

I'd start by commenting out the date clause, if you get no results. Beware also that BETWEEN is inclusive, and can be awkward when working with times on dates, as BETWEEN 2000-01-01 and 2000-01-31 misses off records that happened at e.g. 200-01-31 12:34:56, even though they happened in january too.. It's often better to do date ranging using >= and < unless you're certain the dates are discrete (no times)

I did as you asked. Both the way are not working. I used select CAST(`region`.`district_id` AS SIGNED) for casting. Its generating the o/p now. But have one small issue now. Its just taking 1st no i.e 4. Its leaving 5 and 6 somehow. How can I use all of three no's. Using casting into integer? — user3831445, Jun 23 '20 at 10:52
For debugging those kind of issues you really need to start removing things, like where clauses, or joins; just make it SELECT *, and start commenting things out until you get the number of results you want, then you know what part is faulty. I'd start with the date clause in the where. You didn't provide any example data so there isn't anything more I can really tell you.. If your datatypes of `district_id` in franchise and region are different types, then that points to a fault in your data model really. You aren't supposed to CAST stuff every time you join... — Caius Jard, Jun 23 '20 at 12:30

mysql subquery in where in clause

1 Answers1