Subquery LIMIT causing issues when WHERE outside of subquery implemented

Question

The subquery limits results perfectly when the WHERE clause at the end of the statement is not included. I understand that the subquery LIMIT happens first then the WHERE clause is fired on that result set and that this is a limitation/restriction of a subquery.

What I need is someone more experienced than me to help a brother out with retrieving the records with a LIMIT with the ability to restrict that result set by the WHERE clauses. Let me know if this was not explained well enough.

I also scoured the interwebs in search of the answer for hours with no luck. Your time is appreciated.

EDIT: added a crude example on SQLfiddle: http://sqlfiddle.com/#!9/2de563/4

    SELECT *
    FROM (SELECT * FROM parent_products LIMIT 10) pp
    INNER JOIN products_variants pv ON pv.parent_id=pp.parent_id
    INNER JOIN products p ON p.id=pv.product_id
    INNER JOIN product_types pt ON pt.product_type_id=p.product_type
    LEFT JOIN team_list t ON pp.team_id=t.team_id
    LEFT JOIN photos ph ON ph.product_id=p.id
    LEFT JOIN product_attributes pa ON pa.product_id=pv.product_id
    LEFT JOIN attributes a ON a.id=pa.attribute_id
    LEFT JOIN product_attribute_options po ON po.product_attribute_option_id=a.parent_id
    WHERE t.team_id=100 AND p.active='y';

Explain select:

if possible share your sample data script, will be easy to debug query — Chintan Udeshi, Feb 04 '17 at 07:24
@ChintanUdeshi I wish I could... it is a work database...plus the data structure is jacked. Essentially this is a 1-n relationship with parents/children. I need to LIMIT the parents but not include the children as counting towards the limit (which query above does). But once the WHERE clause is added results are empty due to the LIMIT picking the first 10 results and running the WHERE against those 10 results. Thus no results. Hopefully this explanation helps a bit more. — Binary101010, Feb 04 '17 at 07:34
yes the first 10 results which are selected from (SELECT * FROM parent_products LIMIT 10) are not having Active = 'y' in corresponding Products so no data is coming when you apply where condition — Chintan Udeshi, Feb 04 '17 at 07:46
@ChintanUdeshi the active='y' in this particular scenario isn't the issue. But yes, I think you get my dilemma. I want to LIMIT the returned results after the WHERE clause happens but I require the LIMIT because I want to limit the parents and not the children. I would assume I need the query written differently to still allow the limit on the parent level so I limit 10 parents but get all the children of those parents and then have the WHERE clause run against those limited results. I need to know how this query could be rewritten to do that. — Binary101010, Feb 04 '17 at 07:52
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 04 '17 at 08:27
are you getting desired rows by executing below query SELECT * FROM (SELECT * FROM parent_products LIMIT 10) pp INNER JOIN products_variants pv ON pv.parent_id=pp.parent_id INNER JOIN products p ON p.id=pv.product_id WHERE p.active='y'; — Chintan Udeshi, Feb 04 '17 at 08:50
@Strawberry I added sqlfiddle, apologies. http://sqlfiddle.com/#!9/2de563/4 — Binary101010, Feb 04 '17 at 09:35
@ChintanUdeshi I added sqlfiddle. It is a very crude one.. but it represents the question — Binary101010, Feb 04 '17 at 09:35
yes i checked the fiddle execute the below query and let me know if you get desired results SELECT * FROM (SELECT users.id,users.name FROM users LEFT JOIN map ON users.id=map.user_id LEFT JOIN locations ON locations.location_id=map.location_id INNER JOIN type ON locations.type=type.id WHERE type.id=1 limit 3) users LEFT JOIN map ON users.id=map.user_id LEFT JOIN locations ON locations.location_id=map.location_id INNER JOIN type ON locations.type=type.id where type.id=1; — Chintan Udeshi, Feb 04 '17 at 10:00
@ChintanUdeshi much appreciated. This worked perfectly. Make an answer so I can give you the points :) — Binary101010, Feb 04 '17 at 10:03

score 0 · Accepted Answer · answered Feb 04 '17 at 10:07

0

Below query will give you the expected output

SELECT * FROM (SELECT users.id,users.name FROM users
LEFT JOIN map ON users.id=map.user_id
LEFT JOIN locations ON locations.location_id=map.location_id
INNER JOIN type ON locations.type=type.id
WHERE type.id=1 limit 3) users
LEFT JOIN map ON users.id=map.user_id
LEFT JOIN locations ON locations.location_id=map.location_id
INNER JOIN type ON locations.type=type.id
where type.id=1;

answered Feb 04 '17 at 10:07

Chintan Udeshi

362
1
8

LIMIT without ORDER BY is fairly meaningless – Strawberry Feb 04 '17 at 18:55
output needed first 3 users anyway – Chintan Udeshi Feb 05 '17 at 08:07
This selects 3 rows. But not necessarily the first 3 rows – Strawberry Feb 05 '17 at 08:34
the requirement in the fiddle was to fetch 3 users and all there locations labelled as business so i haven't added order by – Chintan Udeshi Feb 05 '17 at 09:31
That may be so, but it's actually irrelevant. LIMIT without ORDER BY is always meaningless. Just occasionally, it doesn't matter - like when you want to know if something does or doesn't exist at least once in a database - but that's not the case here. – Strawberry Feb 05 '17 at 09:36
i am not getting you what you are trying to explain in this case please check this link http://stackoverflow.com/questions/34812412/is-it-safe-to-use-limit-without-order-by it might help out – Chintan Udeshi Feb 05 '17 at 09:41

Subquery LIMIT causing issues when WHERE outside of subquery implemented

1 Answers1