Thanks all: Patrick selected rev.* and I.* cleared the confusion. The Item actually brought 4 rows hence the final result set had 4 rows.
Regards, Meena On Sun, Oct 22, 2023 at 10:13 AM Bjørn Jørgensen <bjornjorgen...@gmail.com> wrote: > alos remove the space in rev. scode > > søn. 22. okt. 2023 kl. 19:08 skrev Sadha Chilukoori < > sage.quoti...@gmail.com>: > >> 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 >>> >>> >>> > > -- > Bjørn Jørgensen > Vestre Aspehaug 4, 6010 Ålesund > Norge > > +47 480 94 297 >
