Sql query gets result 7 different when it should only have 2

[Sl1ver]

this is my query

select distinct * from
(select distinct city,itm__code,barcode1  from bx_itembarcodemaster
where city = 'jhb'
and (barcode1 is not null and bqty1 is not null)
) jhb
left join
(
select distinct city,itm__code,barcode1 from bx_itembarcodemaster
where city = 'cpt'
and (barcode1 is not null and bqty1 is not null)
) cpt on cpt.itm__code = jhb.itm__code
left join
(
select distinct city,itm__code,barcode1  from bx_itembarcodemaster
where city = 'dbn'
and (barcode1 is not null and bqty1 is not null)
) dbn on dbn.itm__code = jhb.itm__code
 

and this is the result set

JHB    0-16040    0-16040        CPT    0-16040    0-16040        DBN    0-16040    0-16040
JHB    0-16040    0-16040        CPT    0-16040    0-16040        DBN    0-16040    6002303714622
JHB    0-16040    0-16040        CPT    0-16040    6002303714622    DBN    0-16040    0-16040
JHB    0-16040    0-16040        CPT    0-16040    6002303714622    DBN    0-16040    6002303714622
JHB    0-16040    6002303714622    CPT    0-16040    0-16040        DBN    0-16040    0-16040
JHB    0-16040    6002303714622    CPT    0-16040    0-16040        DBN    0-16040    6002303714622
JHB    0-16040    6002303714622    CPT    0-16040    6002303714622    DBN    0-16040    0-16040
JHB    0-16040    6002303714622    CPT    0-16040    6002303714622    DBN    0-16040    6002303714622
 

Its in multiples of 4 at first then 2 then 1 even thought there should technically only be 2 lines returned

WHat am i doing wrong?

[ck9663]

Your LEFT JOIN returns all rows from your quest query regardless if there are matching rows on your right query.

Happy Coding!!!

~~ CK

[Sl1ver]

i tried just normal joining aswell, but it does the same thing

[ck9663]

Have you tried INNER JOIN ?

Happy Coding!!!

~~ CK

[Sl1ver]

Tried all the joins its gives the same result

[ck9663]

Will this work?

 
select distinct city,itm__code,barcode1 
from bx_itembarcodemaster
where city in ('jhb','ctp','dbn') and
(barcode1 is not null and bqty1 is not null)
 

Happy Coding!!!

~~ CK