sql left outer join to add data avoiding cross product

    CREATE TABLE adm
        ([cID] int, 
        [pspID] int)
    ;
        
    INSERT INTO adm
        ([cID], [pspID])
    VALUES
        (1, 89 ),
        (1, 90),
        (1, 90),
        (2, 75),
        (3, 77)
    ;

    CREATE TABLE epi
        ([cnID] int, 
        [ptID] int)
    ;
        
    INSERT INTO epi
        ([cnID], [ptID])
    VALUES
        (1, 890 ),
        (1, 900),
        (1, 900),
        (2, 750),
        (3, 770)
    ;

        CREATE TABLE isc
        ([pID] int, 
        [asnID] int)
    ;
        
    INSERT INTO isc
        ([pID], [asnID])
    VALUES
        (890,89),
        (900,90),
        (750,75),
        (770,77)
    ;

Open in new window


select t1.cID as t1cID
       ,t1.pspID as t1pspID
       ,t2.cnID as t2cnID
       ,t2.ptID as t2ptID

FROM adm as t1
left outer join isc as t3 on t3.pID = t1.pspID and t3.asnID = t1.pspID
inner join epi as t2 on t2.cnID = t1.cID

Open in new window


I'm getting a cross product between t3.asnID x t1.pspID even though I included  t3.asnID = t1.pspID in the left outer join.

If I put the t3.asnID = t1.pspID in a WHERE clause at the bottom then I get nothing.

I want a table where  t3.asnID = t1.pspID. The result should be the three rows in table t1 with the extra information from table t2 and t3. (t3 is my join table which translates IDs from t1 to IDs from t2).
deleydSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
I don't think you can do that because you have an extra row in epi.  Note I converted to temp tables:

select distinct *
FROM #adm adm
join #isc isc on adm.pspID = isc.asnid
join #epi epi on adm.cid = epi.cnid  and isc.pid = epi.ptid

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deleydSoftware EngineerAuthor Commented:
I was mistaken when I said "Three" rows result. Both t1 and t2 have 5 rows. Result should have 5 rows. We're matching rows from t1 with rows in t2. (Actually, if either table has extra rows, I'd want those too, but one problem at a time.)

Thank you for providing the code. Let me have a look at it and I'll get back.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
in that case it should look something like this:
select *
FROM #adm adm
join #epi epi on adm.cid = epi.cnid  
join #isc isc on epi.ptid = isc.pid and adm.pspid = isc.asnid

Open in new window


you're getting the extra rows because you have cnID defined multiple times.  (1,900)

I'm assuming this is just test data?
0
PortletPaulfreelancerCommented:
I don't currently have sql server to test on, but what you are asking for may be possible using CROSS APPLY

This (untested) query using a "correlated subquery" should produce just the 5 wanted rows

SELECT
      adm.*
    , isc.*
    , (select top(1) epi.ptid from epi where adm.cid = epi.cnid  and isc.pid = epi.ptid)
FROM adm
    inner join isc on adm.pspID = isc.asnid

Now, if that worked, so too should the following, the difference being that you can include more columns from epi using the CROSS APPLY

SELECT
      adm.*
    , isc.*
    , ca.*
FROM adm
    inner join isc on adm.pspID = isc.asnid
    cross apply (
      select top(1) epi.*
      from epi where adm.cid = epi.cnid and isc.pid = epi.ptid
      -- order by ????
      ) ca
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.