Solved

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

Posted on 2012-04-08
2
797 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:MK15
2 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
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
 

Author Comment

by:MK15
Comment Utility
Hi,
thanks a lot mate go rid of the error
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now