SQL So i did a sub query which gave a correct result of displaying 3 persons. An

Question

SQL

So i did a sub query which gave a correct result of displaying 3 persons.

And then I was assigned bringing those 3 persons again using sql code but with equi join, but then instead I got 5000rows and more instead of 3.

Here's my Sub query;

select knr,fnamn,enamn
from kund
where knr in (select knr from kundorder where ordnr in (select ordnr
from orderrad where artnr in (select artnr from artikel where vgnr in (select vgnr
from varugrupp where lower (vgnamn) = 'skäggvård' or lower (vgnamn) = 'bondgård'))));

Here's my equi join;

(Also tried with distinct but then I get 10people anyway.

select kund.knr ,kund.fnamn, kund.enamn
from kund, kundorder, orderrad, varugrupp, artikel
where lower (varugrupp.vgnamn) = 'skäggvård'
or lower (varugrupp.vgnamn) = 'bondgård'
and kund.knr = kundorder.knr
and kundorder.ordnr = orderrad.ordnr
and orderrad.artnr = artikel.artnr
and artikel.vgnr = varugrupp.vgnr;

Explanation / Answer

Hi,

The problem seems to be related to the paranthesis and grouping of filters. Below two conditions should be kept together inside the braces. This will help compiler apply these two condtions separately and pevents it to get merged with other conditional logics.

Current-

where lower (varugrupp.vgnamn) = 'skäggvård'
or lower (varugrupp.vgnamn) = 'bondgård'

Correct Way to do-

where (lower (varugrupp.vgnamn) = 'skäggvård'
or lower (varugrupp.vgnamn) = 'bondgård' )

Reason why you are getting more records is - As "or" is not kept in braces, all the "and" condtions that appear after the "or" are treated as an "or" condition and hence it is resulting in more number of rows.

Please use below query-

select kund.knr ,kund.fnamn, kund.enamn
from kund, kundorder, orderrad, varugrupp, artikel
where kund.knr = kundorder.knr
and kundorder.ordnr = orderrad.ordnr
and orderrad.artnr = artikel.artnr
and artikel.vgnr = varugrupp.vgnr
and (lower (varugrupp.vgnamn) = 'skäggvård' or lower (varugrupp.vgnamn) = 'bondgård');

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.