SQL many to many left join

I have three tables:
PROFILE P      PROFILE_PRODUCTLINE   PPL         PRODUCTLINE PL
profileid          profileproductlineid                          productlineid
                       productlineid                                    product
                       profileid      

I want a parameterized query:
all PL.product, PPL.profileproductlineid
where p.profileid = @profileid

Report like this: for profile.profileid = 126
Productline:      
Full                ppl.profileproductlineid
Managed       ppl.profileproductlineid
Lite
Self-Service

Meaning that profileid 126, of all productlines, subscribes to Full and Managed productlines.
worldfearAsked:
Who is Participating?
 
worldfearConnect With a Mentor Author Commented:
select  pl.productline, ppl.profileid
from
      productline as pl
left join
      profile_productline ppl
on
      pl.productlineid = ppl.productlineid
WHERE
      ppl.profileid = 126 OR ppl.profileid IS NULL

strangely, this works.
0
 
Gideon7Commented:
See code snippet.

SELECT PL.product, PPL.profileproductlineid
FROM dbo.PRODUCTLINE PL LEFT JOIN dbo.PROFILE_PRODUCTLINE PPL
    ON PL.productlineid = PPL.productlineid
WHERE PL.profileid = @profileid

Open in new window

0
 
Gideon7Commented:
The PROFILE table didn't have any other columns so I assumed it was redundant.  If not let me know.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
worldfearAuthor Commented:
SELECT PL.productline, PPL.profile_productlineid
FROM dbo.PRODUCTLINE PL LEFT JOIN dbo.PROFILE_PRODUCTLINE PPL
    ON PL.productlineid = PPL.productlineid
WHERE PPL.profileid = 126

The code above (slightly modified) is what I am testing.

Only returns two rows.  PPL is a join table, and not all of the ProductlineIDs are represented for the profileid.  
Profileid = 126
PPL has only 2 records for this profileid.  
0
 
bleach77Commented:
How many records it should have shown?
SELECT PL.productline, PPL.profile_productlineid
FROM dbo.PROFILE_PRODUCTLINE PPL LEFT JOIN dbo.PRODUCTLINE PL
    ON PL.productlineid = PPL.productlineid
WHERE PPL.profileid = 126

Open in new window

0
 
worldfearAuthor Commented:
6 -
productlines:   profileids
lite                   126          
regular             null
managed         126
self service      null
0
 
bleach77Commented:
How many did you get with my code?
0
 
worldfearAuthor Commented:
2
0
 
bleach77Commented:
That is not getting the info associated only with profileid 126 but also with profileid null
Its weird.. :?
Are you sure that's the result you should be getting?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.