problem with joining 2 SELECT Statements

Questionsproblem with joining 2 SELECT Statements
Akhlad asked 5 years ago

the filters automatically adds it's conditions in the WHERE clause of the temp_table joined to the original table, which makes an error every time a user uses a filter
As you can see in the example below

SELECT
mt.id AS id,
mt.number AS number,
CONCAT(p.sku, '-P', mt.number) AS patch_sku,
goods_table.quantity AS quantity,
mt.comments AS comments,
mt.top_organization_id AS top_organization_id,
mt.created,
crea.id AS createdby_id,
mt.modified,
modi.id AS modifiedby_id,
mt.is_active AS is_active
FROM
k9_product_patch mt
LEFT OUTER JOIN
k9_product p ON p.id = mt.product_id
LEFT OUTER JOIN
(SELECT
SUM(mt.quantity) AS quantity, MAX(pp.id) AS product_patch_id
FROM
k9_goods mt
LEFT OUTER JOIN k9_product_patch pp ON pp.id = mt.product_patch_id
WHERE
mt.top_organization_id = 1
AND mt.is_active = 1
AND pp.top_organization_id = 1
AND pp.is_active = 1
AND `crea`.`id` = 'NULL' <—————————————— wrong location of automatic generated condition
AND `mt`.`is_active` = '1' <—————————————— wrong location of automatic generated condition
GROUP BY pp.id) AS goods_table ON goods_table.product_patch_id = mt.id
LEFT OUTER JOIN
k9_user crea ON crea.id = mt.created_by
LEFT OUTER JOIN
k9_user modi ON modi.id = mt.modified_by
WHERE
mt.top_organization_id = 1
AND mt.product_id = 2
<—————————————— instead it should be here

3 Answers
Abu Ghufran answered 5 years ago

Emailed you updated build. It should resolve the case.

Akhlad answered 5 years ago

tried it and it didn't fix the issue. The only change is the part `mt`.`is_active` = '1' is now between brackets (`mt`.`is_active` = '1' )

that's all

Abu Ghufran answered 5 years ago

As updated build resolved this ticket, i am closing this case.

Your Answer

8 + 14 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!