question

Car avatar image
Car asked

Stored procedure problem

I want to use this stored procedure to do a search in the publishers table (database Pubs is used). I want to present the result in a gridview with some columns from table publishers and one column with count titles ..how to solve this? You can see a start to do a solution....but I dont know how to do the joins and group by correctly... it works when there is a pub_id I search for which have titles in table but when I search for a pub_id which dont have any titles in that table I get no result...where have I thought wrong?

The result will look like this Publisher ID Publisher Name City State Country Titles 0877 Binnet & Hardley Washington DC USA 7

Please help!

ALTER PROCEDURE uspGetPublisher                    
(                    
@pub_id char(4)=DEFAULT,                    
@pub_name varchar(40)=DEFAULT,                    
@city varchar(20)=DEFAULT,                    
@state char(2)=DEFAULT,                    
@country varchar(30)=DEFAULT                    
)                    

AS SELECT P.pub_id AS 'Publisher ID',P.pub_name AS 'Publisher Name',P.city AS 'City',P.state AS 'State',P.country AS 'Country' , COUNT(DISTINCT T.title) As 'Antal titlar'
FROM publishers P

INNER JOIN titles T ON P.pub_id = T.pub_id                    
                    
WHERE (P.pub_id LIKE '%'+@pub_id+'%' OR P.pub_name LIKE '%'+@pub_name+'%' OR P.city LIKE '%'+@city+'%' OR P.state LIKE '%'+@state+'%' OR P.country LIKE '%'+@country+'%')                    
GROUP BY P.pub_id, P.pub_name, P.city, P.state, P.country                    
sql-server-2005t-sqlstored-procedures
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image RickD commented ·
So, what result are you after? Can you provide a sample?
0 Likes 0 ·
Car avatar image Car commented ·
what do you mean?
0 Likes 0 ·
Car avatar image Car commented ·
You will search at publisher and you can search in all columns in that table. In the result you will see how many titles a publisher have You will then even see the authors which have a "connection" to the publisher
0 Likes 0 ·
RickD avatar image RickD commented ·
Ok, but how would you like to SEE the data, please post a sample of what you expect and the layout. Edit the original post to add this.
0 Likes 0 ·
Car avatar image Car commented ·
Now it`s edit...
0 Likes 0 ·

1 Answer

·
TG avatar image
TG answered

Looks to me like all you need to do is change your INNER JOIN to a LEFT OUTER JOIN. Then your count(*) will have a 0 for publishers with no rows in Titles

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.