Re: SQL question - duplicate records from join

On Mon, 4 Mar 2002, Raymond O'Donnell wrote:

> Hi folks,
>
> I sent the attached email a few days ago, and if anyone can help me
> I'd be really appreciative - I'm no SQL guru and this one has me
> stumped! If this is the wrong forum for this message please do
> redirect me.

Can you make a small test case that fails for us to look at (on the
assumption that you wouldn't want to send the real schema/query/data)? I
don't see anything particularly wrong with the typical query below, but I
can't really try it and see.

> On 28 Feb 2002 at 18:46, Raymond O'Donnell wrote:
>
> > I recently upgraded to Postgres 7.1 (having finally found the correct
> > pg_dump!), and now I notice that joins are returning duplicate
> > records. A typical query would be:
> >
> > select i.itemcode, i.itemname, d.deptname, r.roomname
> > from items i
> > inner join departments d on (i.deptcode=d.deptcode)
> > inner join rooms r on (i.roomcode=r.roomcode)
> > where... (etc)
> >
> > This query returns four copies of each record, where before I just got
> > one as I'd expect. Am I doing something wrong here?