SQL Server Performance Forum – Threads Archive

Generic Join Question

I have three tables tblPublication (PubID, PubName)
tblMembers (MemberID, MemberEmail)
tblPublicationMembers (PubID, MemberID) I want a list of all the publications, and another column telling me if the member is associated with the publication. If I have two Publications like PubID PubName
—– ——-
1 Test Publication
2 Hope This works and the tblPublicationMembers looks like: PubID MemberID
—– ——–
1 1 I need a query that returns the following for a given MemberID PubID PubName IsAssociated
—– ——- ————
1 Test Publication 1
2 Hope This Works 0
I can’t figure out what type of join I want…..the IsAssociated doesn’t have to be 1 or 0, I can be null or anything else. I get stuck because of the parameter. My initial inclination was to: Select tblPublication.PubID, tblPublication.PubName, tblPublicationMembers
From tblPublication LEFT OUTER JOIN tblPublicationMemebers on tblPublication.PublicationID = tblPlublicationMembers.PublicationID
Where MemberID = 1 But that sure doesn’t work. I only get the one row that they are associated with, but I need all the publications.. Any help will be appreciated.

Try the following: SELECT P.PubID, P.PubName, M.MemberID AS IsAssociated
FROM tblPublication P
LEFT OUTER JOIN tblPublicationMembers M ON P.PubID = M.PubID
The reason you are getting only one row back is because of your "WHERE" clause. Exclude that and you should be fine. You should use aliases because it makes the code easier to read. Let me know if it works. – Tahsin

But how do I specify I certain MemberID.. Your query just gives me a huge list…I need both (2) Publications and the MemberID (IsAssociated)

I am not exactly sure I follow you. Please post what the query I suggested has outputted and from there outline what it is that you want it to do. Try this: SELECT DISTINCT P.PubID, P.PubName, M.MemberID AS IsAssociated
FROM tblPublication P
LEFT OUTER JOIN tblPublicationMembers M ON P.PubID = M.PubID

Well, I have two Publications, but 200 Members.. So your query listed like 300+ records.
I want only the publication and (IsAssociated) for a particular member. So for MemberID 10 I need: PubID, PubName, IsAssociated
1, "Test Publication", 1
2, "Hope This Works", 0 But for only MemberID 10. Not all the memebers. Does that make sense?

OK, I added the "where M.MemeberID = 10" to your query. It only shows the Publications that it is associated with.
I also need the Pubs they aren’t associated with. Sorry for the confusion, it’s hard to explain.

So, you want publications that are not associated with a memberId to return in your query? SELECT P.PubID, P.PubName, M.MemberID AS IsAssociated
FROM tblPublication P
LEFT OUTER JOIN tblPublicationMembers M ON P.PubID = M.PubID AND M.MemberID = 10

Perfect. Thank You.

and this will be more effecient SELECT P.PubID, P.PubName, M.MemberID AS IsAssociated
FROM tblPublication P
LEFT OUTER JOIN tblPublicationMembers M ON P.PubID = M.PubID
Where M.MemberID = 10 Madhivanan Failing to plan is Planning to fail

Not only more efficient, but also yield the correct results. Consider this:
USE Northwind
GO
SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = ‘ALFKI’ SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
AND c.CustomerID = ‘ALFKI’ Now compare the resultsets. The second query is completely wrong as it returns all Customers rows + the matching rows from Orders, thus the resultset contains 96 rows. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks Frank for the TEST you made [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Actually no test, but I thought it’s better to give an example instead of only writing about it. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

We are not talking about efficiency here, but a correct query to output the results.
Madhivanan, your query will not work because Blinden’s initial problem is to return rows
with NULL or 0 values in them that don’t relate back to the publication table. The "WHERE"
clause will prevent you from doing that in your case. I have created a test scenario to show you why: Create 3 tables with the following data: tblPublication:
PubID PubName
———- —————————————-
1 Test Publication
2 Hope This Works
3 test test tblMembers:
MemberID MemberEmail
———- —————-
1 mememail1
2 mememail2
3 mememail3 tblPublicationMembers:
PubID MemberID
———- ———-
1 1
1 11
2 22
2 22
1 10
0 10
2 10
Madhivanan’s Query outputs the following:
PubID PubName IsAssociated
———- —————————————- ————
1 Test Publication 10
2 Hope This Works 10
The correct query needs to output this:
PubID PubName IsAssociated
———- —————————————- ————
1 Test Publication 10
2 Hope This Works 10
3 test test NULL – Tahsin

Dang! Sometimes it helps to read the original question. [xx(] Good catch anyway. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

The ‘dirty’ version: SELECT DISTINCT P.PubId, P.PubName,
CASE WHEN PM.MemberId IS NULL THEN 0 ELSE 1 END IsAssociated
FROM tblMember M
FULL JOIN tblPublicationMember PM ON M.MemberId = PM.MemberId
FULL JOIN tblPublication P ON PM.PubId = P.PubId
WHERE M.MemberId = @MemberId The ‘clean’ version: SELECT P.PubId, P.PubName, 1 IsAssociated
FROM tblPublication P
INNER JOIN tblPublicationMember PM ON P.PubId = PM.PubId
WHERE PM.MemberId = @MemberId
UNION ALL
SELECT P.PubId, P.PubName, 0
FROM tblPublication P
WHERE NOT EXISTS (SELECT t.PubId FROM tblPublicationMember t WHERE t.PubId = P.PubId AND t.MemberId = @MemberId) … or … SELECT P.PubId, P.PubName, 1 IsAssociated
FROM tblPublication P
INNER JOIN tblPublicationMember PM ON P.PubId = PM.PubId
WHERE PM.MemberId = @MemberId
UNION ALL
SELECT P.PubId, P.PubName, 0
FROM tblPublication P
LEFT JOIN (SELECT t.PubId FROM tblPublicationMember t WHERE t.MemberId = @MemberId) PM
ON P.PubId = PM.PubId
WHERE PM.PubId IS NULL

]]>