CODEWITHSUNDEEP
sqlsql-serverjoinleft-joinouter-join
Samar.Abdeen
Samar.Abdeen

Reputation: 47

How to Fix Right Outer Join

I am Trying to make Left Outer Join Use bellow code; The Result Using (LEFT OUTER , RIGHT OUTER, FULL OUTER And INNER JOIN) is The same Result. I am Try to return All Publisher Name Include Those who don't connected with any Books yet!

SELECT P.PublisherName,
    COUNT(B.BookID) AS BookPublished
    FROM LR_Publisher AS P LEFT OUTER JOIN LR_Book As B 
    ON P.PublisherID = B.PublisherID
    WHERE (P.PublisherID = @pPublisherID OR @pPublisherID IS NULL)
    GROUP BY PublisherName

Thanks In Advance

Upvotes: 0

Views: 95

Answers (3)

Alexander Volok
Alexander Volok

Reputation: 5940

Publishers that have no books published are filtered out by GROUP BY. It can be fixed by using GROUP BY ALL:

SELECT P.PublisherName,
COUNT(B.BookID) AS BookPublished
FROM LR_Publisher AS P 
LEFT OUTER JOIN LR_Book As B    ON P.PublisherID = B.PublisherID
-- WHERE (P.PublisherID = @pPublisherID OR @pPublisherID IS NULL)
GROUP BY PublisherName

Upvotes: 1

Manfred Wippel
Manfred Wippel

Reputation: 2076

you could write it this way

select P.PublisherName,
       isnull(bi.BookPublished, 0) as BookPublished
from LR_Publisher as P
    left join (
        select B.PublisherID, Count(B.BookID) BookPublished
        from LR_Book as B 
        where (@pPublisherID is null or B.PublisherID = @pPublisherID)
        group by B.PublisherID
    ) bi on P.PublisherID = Bi.PublisherID
where (@pPublisherID is null or P.PublisherID = @pPublisherID)

I also was not aware of the filtering done by group by. Thank you for the question.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

simple left join will help you want all publisher name including who does not published any book yet 

   SELECT P.PublisherName 
    FROM LR_Publisher AS P LEFT OUTER JOIN LR_Book As B 
    ON P.PublisherID = B.PublisherID

Upvotes: 0

Related Questions