problem with joining 2 SELECT Statements

Questionsproblem with joining 2 SELECT Statements
Akhlad asked 9 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 9 years ago

Emailed you updated build. It should resolve the case.

Akhlad answered 9 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 9 years ago

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

Your Answer

9 + 19 =

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 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

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

Let us improve this post!

Tell us how we can improve this post?