Обсуждение: SQL question - duplicate records from join
Hi all,
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?
Thanks for all your help.
--Ray.
--------------------------------------
Raymond O'Donnell
rod@iol.ie
rod@gti.ie
--------------------------------------
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. --Ray. 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? --------------------------------------------------------- Raymond O'Donnell http://www.iol.ie/~rod/organ rod@iol.ie The Irish Pipe Organ Page ---------------------------------------------------------
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?
Are you sure that the data in your new installation is really the same as in the old, i.e. your data did not get loaded twice?? --- Raymond O'Donnell <rod@iol.ie> 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. > > --Ray. > > 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? > > --------------------------------------------------------- > Raymond O'Donnell > http://www.iol.ie/~rod/organ > rod@iol.ie The Irish Pipe Organ > Page > --------------------------------------------------------- > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________________________ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com
On 4 Mar 2002 at 8:22, Stephan Szabo 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: I got it sorted - it turned out that there was some ambiguity in the values being used to join tables. Sincere thanks to all who replied, both on- and off-list! --Ray. --------------------------------------------------------- Raymond O'Donnell http://www.iol.ie/~rod/organ rod@iol.ie The Irish Pipe Organ Page ---------------------------------------------------------