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