inner join multiplies records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inner join multiplies records

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2007-10-16 : 07:33:13

Hi, i guess u have run into it again..
I have 3 tables

Table 1 is holding word documents :
WordDoc
wd_id integer primary key
wd image

Table 2 is holding parts of the document in a master detail fashion as from one document we can create many parts of it.

WordPartDoc
wpd_id integer primary key
wpd_wd_id integer foreign key from WordDoc
wpd_part image

Table 3 holds the persons or groups to whom we have given a copy of a specific part.

PersonsCopiedAt
pca_id integer primarey key
pca_wpd_id integer foreing key from WordPartDoc
pca_pg_id integer this is the id of the table that holds the persons or the id of the table that holds the group
pca_p_or_g char(1) this is a flag : 'P' for a person, 'G' for a group

Now, here is my problem... I have created the following sql query

select wd_id, wpd_id
from wordDoc
inner join wordPartDoc on wpd_wd_id = wd_id
inner join PersonCopiedAt on pca_wpd_id = wpd_id
where pca_pg_id = someID and pca_p_or_g = 'G' --or 'P'

This works perfectly if a pca_pg_id is provided, but this is provided using a combobox... If the user doesn't select a person - and thus - no id is provided - inner join brings me as many records of wpd_id as the number of persons that were given a copy. I understand that this is the correct behaviour but in the later case (when no id is provided) i want only one record to be returned. Is there a way to do so ? do i have to redisign the db schema ?

thanx
theodore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:53:16

TOP 1?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:53:20

"i want only one record to be returned."

If there are multiple documents, in this scenario, WHICH one do you want returned?

You can probably replace the table join with a join to a nested select doing a:


FROM MyTable AS T
JOIN
(
    SELECT TOP 1 MyID, MyCol1, MyCol2
    FROM MyTable
    WHERE ...
    ORDER BY MyCol
) AS X
    ON X.MyID = T.MyID

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:54:37

"f the user doesn't select a person - and thus - no id is provided - inner join brings me as many records"

Actually I would expect it to bring you nothing! unless you had an OR in there to handle the scenario where "someID" was blank/null

dev45
Yak Posting Veteran

54 Posts

Posted - 2007-10-16 : 08:21:16

Yes you are right about returning no records. Actually if a person is not selected the pca_pg_id = someID part of the where clause is ommitted. Sorry, my mistake.
Let's now see an example
If we have 2 documents, and 3 parts of each that makes us 6 parts, say d1_p1, d1_p2, d1_p3, d2_p1, d2_p2 and d2_p3
if each of them are given as copies to 2 groups (g1 and g2) and 2 persons (r1 and r2) we have
d1_p1_g1, d1_p1_g2, d1_p1_r1, d1_p1_r2

d1_p2_g1, d1_p2_g2, d1_p2_r1, d1_p2_r2

same for d1_p3, d2_p1, d2_p2, d2_p3 but we dont need to write them all

if a person is selected, say r1 then i would expect the following result :
d1_p1_r1, d1_p2_r1, d1_p3_r1, d2_p1_r1, d2_p1_r1, d2_p2_r1

if no person or group is selected i would expect the following
d1_p1, d1_p2 ,d1_p3, d2_p1, d2_p2, d3_p3

I hope that didnt become to complicated (i know it's not very well said)

Anyway, i used group by clause.... and i think i'll make it work the way i want.... (just havent achieved that yet)

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 10:11:12

"Actually if a person is not selected the pca_pg_id = someID part of the where clause is ommitted"

got'cha, with dynamic SQL that will work OK.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources