Solved

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

Posted on 2012-04-08
2
829 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Install MySQL 5.6 and PHP on Centos Linux 6 101
MySQL Init Waits 25 82
MySQL: Updating SubQuery Match Faster 9 46
MySqlDump not dumping triggers 1 17
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

25 Experts available now in Live!

Get 1:1 Help Now