Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-04-08
2
872 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
ID: 37822322
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
ID: 37824061
Hi,
thanks a lot mate go rid of the error
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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