Two optional FK to the same table

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

General SQL Server Forums

New to SQL Server Programming

Two optional FK to the same table

Author

Topic

vitoco
Starting Member

22 Posts

Posted - 2009-12-15 : 17:37:22

I have the following situation:

Table B is a NAV table between A and C. One PK from A may not appear or appear one or more times on B with different PKs from C.

All Id fields are of the same type. PK's are:
- A (IdA)
- B (IdA,IdC)
- C (IdC)

Sample Data:


Table A    Table B    Table C
  IdA      IdA IdC      IdC
   1        1   1       1
   2        2   1       2
   3        2   2       3
   4

I need a SELECT that returns the following rows:


IdA IdC1 IdC2
 1    1  null
 2    1   2
 3  null null
 4  null null

I tried many combinations of LEFT OUTER JOINs and conditions, but get rows like the following or no rows at all:


IdA IdC1 IdC2
 1    1   1
 2    1   2
 2    1   1
 2    2   1
 2    2   2

Any hint?

singularity
Posting Yak Master

153 Posts

Posted - 2009-12-16 : 01:42:04

select a.IdA, b.IdC as IdC1, c.IdC as IdC2
from tblA a
left join tblB b on a.IdA = b.IdA
left join tblC c on b.IdC = c.IdC

vitoco
Starting Member

22 Posts

Posted - 2009-12-16 : 08:13:20

quote:
Originally posted by singularity

select a.IdA, b.IdC as IdC1, c.IdC as IdC2
from tblA a
left join tblB b on a.IdA = b.IdA
left join tblC c on b.IdC = c.IdC

That gives:


IdA     IdC1    IdC2
1	1	1
2	1	1
2	2	2
3	NULL	NULL
4	NULL	NULL

Only one row for IdA=2 must be present.

What I want to do is to restore the original table A before a normalization of it, which had an attribute field that appeared many times and were moved to a clasification table C. Attribute order is not important at this time, and most of the records have no attributes, many of them have only one, and a few have more than one (I must choose only two of them "randomly").

The best I could do was:


select a.IdA, b1.IdC as IdC1, b2.IdC as IdC2
from A a
left join B b1 on b1.IdA = a.IdA
--left join C c1 on c1.IdC = b1.IdC
left join B b2 on b2.IdA = a.IdA
--left join C c2 on c2.IdC = b2.IdC
where b1.IdC <= b2.IdC or b2.IdC is null
--and isnull(c1.T,1) = 1
--and isnull(c2.T,1) = 1

that gives:


IdA     IdC1    IdC2
1	1	1
2	1	1
2	1	2
2	2	2
3	NULL	NULL
4	NULL	NULL

I'm trying to not post-process this output to fix the record for IdA=1 (nullify IdC2) and IdA=2 (keep only the record with different values for IdC1 and IdC2). If I have to post-process, probably is much simpler a query with only one outer join, to get fewer rows.

BTW, inner joins to C are commented out to simplify this situation, but is required because I have to filter out some records of C based on another field of it. Is there another way to write this conditions without using this isnull() trick for the outer join of C?

Thanks!

singularity
Posting Yak Master

153 Posts

Posted - 2009-12-16 : 20:34:31

Not sure if I understood correctly, but if you're using SQL Server 2005, you can try something like this:


select IdA, IdC1, IdC2
from
(select a.IdA, b.IdC as IdC1, c.IdC as IdC2, 
        row_number() over (partition by a.IdA order by newid()) as rn
from tblA a
left join tblB b on a.IdA = b.IdA
left join tblC c on b.IdC = c.IdC)
where rn = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources