Hi Meena, I'm asking to clarify, are the *on *& *and* keywords optional in the join conditions?
Please try this snippet, and see if it helps select rev.* from rev inner join customer c on rev.custumer_id =c.id inner join product p on rev.sys = p.sys and rev.prin = p.prin and rev.scode= p.bcode left join item I on rev.sys = I.sys and rev.custumer_id = I.custumer_id and rev. scode = I.scode; Thanks, Sadha On Sat, Oct 21, 2023 at 3:21 PM Meena Rajani <meenakraj...@gmail.com> wrote: > Hello all: > > I am using spark sql to join two tables. To my surprise I am > getting redundant rows. What could be the cause. > > > select rev.* from rev > inner join customer c > on rev.custumer_id =c.id > inner join product p > rev.sys = p.sys > rev.prin = p.prin > rev.scode= p.bcode > > left join item I > on rev.sys = i.sys > rev.custumer_id = I.custumer_id > rev. scode = I.scode > > where rev.custumer_id = '123456789' > > The first part of the code brings one row > > select rev.* from rev > inner join customer c > on rev.custumer_id =c.id > inner join product p > rev.sys = p.sys > rev.prin = p.prin > rev.scode= p.bcode > > > The item has two rows which have common attributes and the* final join > should result in 2 rows. But I am seeing 4 rows instead.* > > left join item I > on rev.sys = i.sys > rev.custumer_id = I.custumer_id > rev. scode = I.scode > > > > Regards, > Meena > > >
