?
Solved

SQL many to many left join

Posted on 2009-04-15
9
Medium Priority
?
750 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:worldfear
  • 4
  • 3
  • 2
9 Comments
 
LVL 12

Expert Comment

by:Gideon7
ID: 24153741
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
 
LVL 12

Expert Comment

by:Gideon7
ID: 24153750
The PROFILE table didn't have any other columns so I assumed it was redundant.  If not let me know.
0
 

Author Comment

by:worldfear
ID: 24154119
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Expert Comment

by:bleach77
ID: 24154331
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
 

Author Comment

by:worldfear
ID: 24154339
6 -
productlines:   profileids
lite                   126          
regular             null
managed         126
self service      null
0
 
LVL 4

Expert Comment

by:bleach77
ID: 24154362
How many did you get with my code?
0
 

Author Comment

by:worldfear
ID: 24154363
2
0
 

Accepted Solution

by:
worldfear earned 0 total points
ID: 24154639
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
 
LVL 4

Expert Comment

by:bleach77
ID: 24155553
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question