March 1, 2005 at 11:58 am
Hello,
I have the following query that brings the same record twice! I don't understand why the record shows up twice when all the values are the same!
SELECT * FROM PSM
INNER JOIN BR.DBO.PM ON PSM.LISTAID = PM.LISTANBR
INNER JOIN BR.DBO.OFFICE OS ON PSM.LISTOID = OS.LISTOLSNBR
INNER JOIN BR.DBO.PROFFICEMAP POM ON OS.OFFICEID = POM.OFFICEID AND PM.PRID = POM.PRID
WHERE PGUID = ('{BA9687DC-C465-428A-A49E-5927F592JLC0}')
Any suggestions on why I get a duplicate record? The JOINS are not right??
Thanks.
JN
March 1, 2005 at 12:04 pm
Post the DDL, relationships between the tables and sample data.
You could use SELECT DISTINCT *, which will not run as quickly ... and won't solve the underlying problem of either bogus duplicated records, or a lack of understanding on the query author's part regarding how the tables relate to each other & their cardinality.
March 1, 2005 at 12:40 pm
Following values are returned by the query:
PGUID: BA9687DC-C465-428A-A49E-5927F592JLC0
LISTAID: 240509
LISTOID: 03396
LISTANBR: 240509
PRID: 3520
LISTOLSNBR: 03396
OfficeID: 26
PGUID: BA9687DC-C465-428A-A49E-5927F592JLC0
LISTAID: 240509
LISTOID: 03396
LISTANBR: 240509
PRID: 3520
LISTOLSNBR: 03396
OfficeID: 26
PSM table has records with agentID and OfficeID. I want a record from PSM with an agentID that is working for the record's Office so has a corresponding OfficeID in JOINED office table. It so happens that a record is having an agentID that is found to be same for two agents, but they work for different offices so I want the record with an agentID and officeID matching, which is coming by the query, but somehow, it's bringing that same record twice. So LISTAID is a common field between PSM and PM table. LISOID is common between PSM and OS table. OS and PM table have nothing in common. POM, however, has two fields that are found each in PM table and OS table so that joins those two table. But somehow, this JOIN brings the same record twice.
Does this clarify? If more questions, please let me know.
Thanks.
JN
March 1, 2005 at 12:51 pm
Also, there are no duplicate or bogus records in PSM table. Each record is unique with a unique PGUID.
March 1, 2005 at 12:57 pm
Yes, but you are not even joining on PGUID so the join columns are also of primary interest.
Your DDL is a bit too annotated... Would you mind scripting your three tables (though Enterprise Manger - it takes no time at all) and posting that DDL?
March 1, 2005 at 12:58 pm
This is how I would try to pinpoint the problem :
take the query and remove 1 table from join at the time... eventually you'll be getting only 1 record. That'll tell you in which table to look for the duplicate. It's most likely that you hav 2 offices when you think you have only 1 ore something simple like that.
March 1, 2005 at 1:16 pm
I figured it out! There was another table that I had to JOIN on and now I get only one record. The value that was different for the identical record was something I didn't care for, but it happened to be the only unique value for the same record so eventhough it was duplicated this one field had different value, which I wasn't seeing.
Anyway, thanks for trying to help. Working with this project is a nightmare. Data is spread across million tables in many different databases and ID1 could mean one thing in one table and something totally different in another table so my brain gets "fried" figuring out relationships between tables, as there is no documentation or data dictionary.
March 1, 2005 at 1:33 pm
Welcome to my world.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply