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
