mysql, view, ERROR 1242 (21000): Subquery returns more than 1 row

Hi can someone help me with this please,

i have a view where i am joining up several tables so is easier to query, i seem to be having this error stating subquery returns more than 1 row.

create view full_book_view as select b.book_id, b.book_title,b.isbn,b.retail_price, b.new_price, b.postage_price, b.description, b.quantity, b.book_condition
			    , (select group_concat(a.author) FROM book_authors as ba 
			    inner join author as a on ba.author_id = a.author_id
			    WHERE ba.book_id = b.book_id
			   GROUP BY ba.book_id) as Author,
			   (select group_concat(pub.publisher) from book_publishers as pb
			   inner join publisher as pub on pb.publisher_id = pub.publisher_id
			   where pb.book_id = b.book_id group by pb.book_id) as Publisher,
			   (select url from book_url as bu where bu.book_id = b.book_id)as url ,(select cust.username from customer as cust inner join users_per_book as upb on upb.customer_id = cust.customer_id where upb.book_id = b.book_id) as customer
			from books as b
			inner join book_categories as bc on bc.book_id = b.book_id
			inner join categories as cat on cat.cat_id = bc.category_id;

Open in new window


thanks
MK15Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Any one of the subqueries could result in returning more than value, but because you put them in the select clause, they need to always return only one value.  I guess you write your SQL this way because it was possible that a book's author, publisher, url, or customer is not defined?  Then you should use left outer joins.  Try:

create view full_book_view as 
select b.book_id, b.book_title,b.isbn,b.retail_price, b.new_price, b.postage_price, b.description, b.quantity, b.book_condition, 
    a.author, p.publisher, bu.url, c.username as customer
			from books as b
			inner join book_categories as bc on bc.book_id = b.book_id
			inner join categories as cat on cat.cat_id = bc.category_id
                        left outer join (select ba.book_id book_id, group_concat(a.author) author
                                         FROM book_authors as ba 
			                 inner join author as a on ba.author_id = a.author_id
			                 GROUP BY ba.book_id) a
                            on b.book_id = a.book_id
                        left outer join (select pb.book_id book_id, group_concat(pub.publisher) publisher 
                                        from book_publishers as pb
			                inner join publisher as pub on pb.publisher_id = pub.publisher_id
			                group by pb.book_id) p
                            on b.book_id = p.book_id
                        left outer join book_url bu on b.book_id = bu.book_id
                        left outer join (select upb.book_id book_id, cust.username 
                                         from customer as cust 
                                        inner join users_per_book as upb on upb.customer_id = cust.customer_id) c
                            on b.book_id = c.book_id;

Open in new window


NOTE: obviously untested, but should give you a good idea how you should proceed from here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MK15Author Commented:
Hi,
thanks a lot mate go rid of the error
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.