Re: Complex Query
Hi Mimi, try this select count(*) from (SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)), pkisbn, publisher,publication_year, edition, cover_type, pages FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = ? group by IFNULL(image,'no_image.jpg'),title, subtitle, pkisbn, publisher); Guido Mimi Cafe schrieb im Newsbeitrag news:004701cc16f1$a2d550c0$e87ff240$@com... Hi I now need to determine the number of rows returned by this query below. Wrapping it within SELECT COUNT did not work as expected. SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)), pkisbn, publisher,publication_year, edition, cover_type, pages FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = ? group by IFNULL(image,'no_image.jpg'),title, subtitle, pkisbn, publisher; Mimi From: Mimi Cafe [mailto:mimic...@googlemail.com] Sent: 20 May 2011 11:33 To: 'Anupam Karmarkar' Cc: 'Guido Schlenke'; mysql@lists.mysql.com Subject: RE: Complex Query Hi guys That's cool! It looks like my query was good except that I miss the group by. Now I only had to remove the as image from the grouping below and it works fine. Thanks guys Mimi From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: 20 May 2011 07:48 To: 'Guido Schlenke'; mysql@lists.mysql.com; Mimi Cafe Subject: RE: Complex Query Hi Guido, You need to add group by in your query get desire result SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = 'MSING0010' group by IFNULL(image,'no_image.jpg') as image,title, subtitle, pkisbn, publisher It should give desire result. As group concat will return group by 1 if you dont specify group by, which will return only one row, to get result you need to specify group by as given in above query. --Anupam --- On Fri, 20/5/11, Mimi Cafe mimic...@googlemail.com wrote: From: Mimi Cafe mimic...@googlemail.com Subject: RE: Complex Query To: 'Guido Schlenke' gschle...@hotmail.de, mysql@lists.mysql.com Date: Friday, 20 May, 2011, 5:07 AM Hi Guido I like your powerful query below, but it doesn't answer my question. I guess some clarification is needed here. OK, I have books in the database with more than one author. When I query for the title of books and name of author, I get more rows for books that have more than one author. I need one row per book even if there is more than one author (concatenate the names of all the authors as authors). From the tables below, it can be seen that the book titled Technology Ventures: From Idea to Enterprise is written by three authors. I want to get the record for the book, including the names of all three authors in one row. For my purchase, I got as far retrieving all the information I need (except the names of authors) using the following query: mysql SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, concat(fname,' ',initial,' ',lname) as author, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = 'MSING0010'; +--+--+--+-- -+---++ | image| title| subtitle | author| pkisbn| publisher | +--+--+--+-- -+---++ | no_image.jpg | Financial Intelligence for Entrepreneurs | | Karen Berman | 9781422119150 | Harvard Business Press | | no_image.jpg | Financial Intelligence for Entrepreneurs | | Joe Knight | 9781422119150 | Harvard Business Press | +--+--+--+-- -+---++ In the above example, I get 2 correct rows, but I want to have one row for this, with the names of both authors concatenated
Re: Complex Query
Hi Mimi, I'm not quite sure, if I did understand you right, but let's try the following query: select a.firstname, a.lastname, b.subject from Author a, book_author ba, book b where a.fkauthor_id IN ( select fkauthor_id, count(*) from book_author group by fkauthor_id having count(*) 1 ) and a.fkauthor_id = ba.fkauthor_id and ba.fkisbn = b.pkisbn; Hope it works. Best regards Guido Mimi Cafe schrieb im Newsbeitrag news:000101cc1611$8ba4a4e0$a2edeea0$@com... The tables have following structure. Both keys in table book_author are not primary keys (should they?). Author book_author book publisher subject subject_book == == == = fkauthor_id fkisbn pkisbn pkpublisher_id pksubject_id fksubject_id firstname fkauthor_id fkpublisher_id subject fkisbn middlename lastname Now, for reason unknown to me, the reference between the first 3 tables don't seem to exist although I have created them. My query works for the most of it, but selecting all the authors for a book in one query seems complex and I don't know how to go about it except to use a second query to retrieve the authors names. Also, any ideas why the relation between the first 3 tables don't work? In mysql, I run the command similar to the one below to establish the relationships between the tables (without error), but in MySQL Workbench, I cannot see the relationship between the first three tables above. When I try to create the relations in Workbench it failed. It tells me no primary keys columns in book_author. alter table book_author add foreign key (fkisbn) references book (pkisbn); # the works in mysql without error, but no relation can be seen when the module is created in mysql workbench. Thanks Mimi = -Original Message- = From: George Pitcher [mailto:george.pitc...@publishingtechnology.com] = Sent: 19 May 2011 06:42 = To: Mimi Cafe = Subject: RE: Complex Query = = Mimi, = = I have a lot of biblio records across several applications. I decided = at = the outset not to break up the author names. As long as you are able = to = identify that an author is associated with a book, it should be = straightforward to extract the names. The difficulty that I see = (without = knowing your table structure, is how to get the author names to appear = in the correct order. = = If I was setting this up from scratch, I would use a third table and = in = this I would store the author id, book id and the name position. From = the three, it should be possible to construct the concatenated author = names. = = I tend to do the concatenation of such things in PHP rather than sql, = but that's just my way. = = As for the multiple rows, try using distinct(). = = Hope this helps = = George = = -Original Message- = From: Mimi Cafe [mailto:mimic...@googlemail.com] = Sent: 18 May 2011 22:23 = To: mysql@lists.mysql.com = Subject: Complex Query = = Hi = = I am trying to retrieve record from 2 tables (book and author), but my = problem is how to retrieve all the names of authors where more than = one = author wrote a book. = = Here is what i have: it works OK, except that it returns more than one = row = for books that have more than one author. = = select concat(fname,' ',initial,' ',lname) from author inner join = book_author on fkauthor_id = pkauthor_id inner join book on fkisbn = = pkisbn; = = Any idea who can formulate my query to retrieve the books with with = names of = all the authors concatenated in one? = = = Mimi = = The information in this message is intended solely for the addressee = and should be considered confidential. Publishing Technology does not = accept legal responsibility for the contents of this message and any = statements contained herein which do not relate to the official = business of Publishing Technology are neither given nor endorsed by = Publishing Technology and are those of the individual and not of = Publishing Technology. This message has been scanned for viruses using = the most current and reliable tools available and Publishing = Technology excludes all liability related to any viruses that might = exist in any attachment or which may have been acquired in transit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysq...@m.gmane.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complex Query
Hi Mimi, I'm sorry, but my query doesn't work. In between I created 3 tables: authors: +---+--+--+-+-+ | Field | Type | Null | Key | Default | Extra +---+--+--+-+-+ | a_id | int(10) unsigned | NO | PRI | NULL| auto_increment | FirstName | varchar(50) | YES | | NULL| | LastName | varchar(50) | YES | | NULL| book_authors: +---+--+--+-+-+-- | Field | Type | Null | Key | Default | Extra +---+--+--+-+-+-- | isbn | int(10) unsigned | YES | | NULL| | au_id | int(10) unsigned | YES | | NULL| books: +-+--+--+-+-+-- | Field | Type | Null | Key | Default | Extra +-+--+--+-+-+-- | b_isbn | int(10) unsigned | YES | | NULL| | subject | varchar(50) | YES | | NULL| Suppose we have the following data: select * from books; ++- | b_isbn | subject ++- | 1 | Der Schneemann | 2 | Leopard | 3 | Dschungelbücher | 4 | Brandmauer select * from book_authors; +--+---+ | isbn | au_id | +--+---+ |1 | 1 | |2 | 1 | |3 | 4 | |4 | 3 | select * from authors; +--+---+--+ | a_id | FirstName | LastName | +--+---+--+ |1 | Jo| Nesbo| |2 | Helene| Tursten | |3 | Hakan | Nesser | |4 | Rudyard | Kipling | As you can see, there is only one author that has written more than one title: Jo Nesbo (Schneemann Leopard). If you perform the query SELECT a.a_id, a.LastName, a.FirstName, b.subject FROM AUTHORS a, book_authors ba, books b WHERE a.a_id IN ( SELECT au_id FROM ( SELECT au_id, COUNT(*) FROM `book_authors` GROUP BY au_id HAVING COUNT(*) 1 ) t2 ) AND ba.au_id = a.a_id AND ba.isbn = b.b_isbn; you’ll get the answer: +--+--+---++ | a_id | LastName | FirstName | subject| +--+--+---++ |1 | Nesbo| Jo| Der Schneemann | |1 | Nesbo| Jo| Leopard| +--+--+---++ 2 rows in set (0.00 sec) Is this what you want? I supposed that the isbn numbers in tables ‘book_authors’ and ‘books’ are identically. Best regards, Guido Mimi Cafe schrieb im Newsbeitrag news:000101cc1611$8ba4a4e0$a2edeea0$@com... The tables have following structure. Both keys in table book_author are not primary keys (should they?). Author book_author book publisher subject subject_book == == == = fkauthor_id fkisbn pkisbn pkpublisher_id pksubject_id fksubject_id firstname fkauthor_id fkpublisher_id subject fkisbn middlename lastname Now, for reason unknown to me, the reference between the first 3 tables don't seem to exist although I have created them. My query works for the most of it, but selecting all the authors for a book in one query seems complex and I don't know how to go about it except to use a second query to retrieve the authors names. Also, any ideas why the relation between the first 3 tables don't work? In mysql, I run the command similar to the one below to establish the relationships between the tables (without error), but in MySQL Workbench, I cannot see the relationship between the first three tables above. When I try to create the relations in Workbench it failed. It tells me no primary keys columns in book_author. alter table book_author add foreign key (fkisbn) references book (pkisbn); # the works in mysql without error, but no relation can be seen when the module is created in mysql workbench. Thanks Mimi = -Original Message- = From: George Pitcher [mailto:george.pitc...@publishingtechnology.com] = Sent: 19 May 2011 06:42 = To: Mimi Cafe = Subject: RE: Complex Query = = Mimi, = = I have a lot of biblio records across several applications. I decided = at = the outset not to break up the author names. As long as you are able = to = identify that an author is associated with a book, it should be = straightforward to extract the names. The difficulty that I see = (without = knowing your table structure, is how to get the author names to appear = in the correct order. = = If I was setting this up from scratch, I would use a third table and = in = this I would store the author id, book id and the name position. From = the three, it should be possible to construct the concatenated author = names. = = I tend to do the concatenation of such things in PHP rather than sql, = but that's just my way. = = As for the multiple rows, try using distinct(). = = Hope this helps = = George = = -Original Message- = From: Mimi Cafe
Re: Oracle imports into MySQL
Hi Machiel, I'm not sure if you like the method I use for Export from Oracle to MySQL databases: You need an ODBC DSN for each, source and destination DB. Then you create an empty Access Database with a link to the Oracle Source table. If the destination MySQL table doesn't yet exists, you can export the linked oracle table directly into the existing ODBC-DSN of the MySQL DB. If (later on) the destination MySQL table exists, you can create an Add-Query that inserts selected rows from the Oracle table to the end of the MySQL table. These actions could be placed into macros (Access 'autoexec' for example) and in scheduled jobs of your operating system (I hope it's Windows, because you didn't say anything about that). If you don't like the Access built-in Visual Basic language, you can use any other programming language that has components to access to ODBC databases like Borland/Embarcadero C++Builder/Delphi or Microsoft Visual C++ etc. Hope this helps. Guido Machiel Richards machi...@rdc.co.za schrieb im Newsbeitrag news:1289457988.2320.27.ca...@machielr-laptop... Good day all I am hoping that someone has got some more answers for me on the topic as most of the websites which have not been very useful. All websites I have found thus far reffers to software that either needs to be bought or otherwise need to be run manually. One of our clients are currently running MySQL for their web based systems, however all other systems are running oracle. There is a current data load process from oracle that generates a dump file of specific data, goes through a convertion process, gets imported into a mysql runnign on VM to test import, then gets pushed to MySQL production. This process was put in place quite some time ago by developers. At some stage I read something about this process not being required from MySQL 5 onwards and data imports from oracle is less troublesome. The import process needs to run every 30 minutes and the current process is too troublesome. We are busy plannign a hardware migration for the systems and are also looking at improving these processes. Does anybody have experience with this to perhaps provide me with some info on how we can improve this import process? Any assistance will be appreciated. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
