October 11, 2016 at 9:14 am
Hi, I have a query where I need to Select data from two tables using inner join.
The problem is that I need to filter it.
This query give me the data that I need.
SELECT bi.ref,st.design,bi.qtt,bi.qtt2,st.fornecedor,st.epcult,st.forref,st.usr1 FROM bi INNER JOIN st ONbi.ref=st.ref AND bi.ndos=16 AND bi.bifref='16.128' AND qtt2 < qtt AND st.design LIKE '%sup%' => (47 rows)
But I need to have more than one "searchable" into the query, for example:
SELECT bi.ref,st.design,bi.qtt,bi.qtt2,st.fornecedor,st.epcult,st.forref,st.usr1 FROM bi INNER JOIN st ONbi.ref=st.ref AND bi.ndos=16 AND bi.bifref='16.128' AND qtt2 < qtt AND st.design LIKE '%sup%' OR st.fornecedor LIKE '%sup%' => (....)
and here is the problem, the result is higher than the real result that I need.
I need to resolve this, because it will work with DataTables.js so I need more than one searchable condition.
Thank you.
October 11, 2016 at 9:34 am
That's all a bit vague, although if I had to guess I'd say that you're missing some parentheses around your AND and OR conditions. ANDs are evaluated before ORs in their absence.
John
October 11, 2016 at 9:46 am
As an aside, avoid using a leading wildcard in your LIKE (i.e. '%sup%) if possible. You won't be able to make use of any indexes on that column if there are any.
October 11, 2016 at 9:58 am
As mentioned before, check the parentheses, here is a suggestion
😎
SELECT
bi.ref
,st.design
,bi.qtt
,bi.qtt2
,st.fornecedor
,st.epcult
,st.forref
,st.usr1
FROM bi INNER JOIN st
ON bi.ref = st.ref
AND bi.ndos = 16
AND bi.bifref = '16.128'
AND qtt2 < qtt
WHERE
(
st.design LIKE '%sup%'
OR
st.fornecedor LIKE '%sup%'
)
October 11, 2016 at 11:30 am
Thank you very much Eirikur Eiriksson.
I had an erro and your suggestion helped me.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply