MYSQL multiple select from multiple tables

Question

I have X tables and I want a single query to get all data which I want. The reason I want this method is I have more than 10.000 records in my database, so I must use only 1 query, because if I use 3-4 query and ten gether the data, it would take really long time to be executed by the server.

So the tables:

** book_info **    
bookId   status        title
------   ------        -----
101      available     How to cook
102      available     IT tips and tricks
103      unavailable   How to use your calc
etc...


** book_writers **    
bookId   writer
------   ------
101      Tom
102      Mike
103      Mike
etc...


** book_log **    
bookId   action   client   date
------   ------   ------   ----
103      loan     Fred     2017-6-10
102      loan     Anna     2017-6-8
101      return   Anita    2017-5-3
101      loan     Anita    2017-2-2
etc...

And what I want in only one query:

bookId   status      title                writer   lastLoan   lastReturn
------   ------      -----                ------   --------   ----------
101      available   How to cook          Tom      2017-2-2   2017-5-3
102      available   IT tips and tricks   Mike     2017-6-8   -
etc...

I tried it by using JOIN and UNION, but I could not do it.

UPDATE

So since I got good solutions from you guys, I finally built the query that I have looked for. But one thing that I can not do is using "parent" query variable in "child" join query.

So I am having a table which contains the content of the books:

** book_content **    
bookId   content
------   -------
101      Lorem ipsum dolor sit amet, consectetur...
102      Donec ut lacus non odio blandit molestie
103      Interdum et malesuada fames ac ante ipsum...
etc...

I tried to query it like

select
  bi.bookId
  bd.content
from
  books_info as bi
left outer join
(
  select
    bookId
    content
  from
    book_content
  where
    bookId = bi.bookId
) bd on bi.bookId = bd.bookId

But the SQL says #1054 - Unknown column 'bi.bookId' in 'where clause' in the join select. How can I use bi.bookId in child join query?

looks like simple query with a few joins, a group_concat for writers, and a subselect for loan/return to me. — Tuncay Göncüoğlu, Jun 10 '17 at 10:41
i have posted query, check the query i have exected it's working for me. — Santosh, Jun 10 '17 at 11:17

ScaisEdge · Answer 1 · 2017-06-10T11:01:46.963

0

You can use inner and left join using two time book_log

select i.bookId, i.status, i.title, w.writer, max(l1.date) lastLoan,  max(l2.date) lastReturn
from book_info as i
inner join book_writers w on i.bookId = w.bookId
inner join book_log l1 on i.book_id = l1.book_id and l1.action ='load'
left join  book_log l2 on i.book_id = l2.book_id and l2.action ='return'
group by i.bookId, i.status, i.title

and (as suggested by Tuncay in the comment below)

use a group_concat if have more then one authot

select i.bookId, i.status, i.title, group_concat(w.writer), max(l1.date) lastLoan,  max(l2.date) lastReturn
from book_info as i
inner join book_writers w on i.bookId = w.bookId
inner join book_log l1 on i.book_id = l1.book_id and l1.action ='load'
left join  book_log l2 on i.book_id = l2.book_id and l2.action ='return'
group by i.bookId, i.status, i.title

edited Jun 10 '17 at 11:01

answered Jun 10 '17 at 10:42

ScaisEdge

131,976
10
91
107

I'd suggest a group concat on writers (books might have more than one author, ie bookId in that table might not be unique). Also joining book_log twice might not necessarily return loan and return from same (last) row, so I'd suggest a subquery on join for that. – Tuncay Göncüoğlu Jun 10 '17 at 10:48
Can you please write it? – Jun 10 '17 at 10:51
.. answer uodated with group concat too and add the missing group by – ScaisEdge Jun 10 '17 at 10:52
Thank you! Anyways it says "action" is ambigous. – Jun 10 '17 at 10:55
Is it typo "t1" rather than "l1"? – Jun 10 '17 at 11:00
Thanks for your help! I tried it but it says I have to user "SET SQL_BIG_SELECTS = 1;". I put it at the beggining of the code but now it timeouts. :( – Jun 10 '17 at 11:09
check for timeout duration and eventually increase it – ScaisEdge Jun 10 '17 at 11:13

Santosh · Answer 2 · 2017-06-11T07:43:05.893

0

try this query hope it will work.

SELECT bi.bookId,status,title,writer,bl.lastLoan,
           bl.lastReturn,bl.totalLoan,bl.totalReturn
from book_info bi
left outer join book_writers as bw on bi.bookId = bw.bookId
left outer join(
SELECT 
bookId,
COUNT(CASE WHEN action = 'loan' THEN action END) AS totalLoan,
COUNT(CASE WHEN action = 'return' THEN action END) AS totalReturn,
 CASE
        WHEN action = 'loan' THEN date
END AS lastLoan,
    MAX(CASE
        WHEN action = 'return' THEN date
END) AS lastReturn

FROM book_log
group by bookId

)  bl on bi.bookId = bl.bookId
group by bi.bookId

If you have book content it will display otherwise it will display null.

select
  bi.bookId
  bd.content
from
  books_info as bi
left join book_content as bd on bi.bookId = bd.bookId
group by bi.bookId

EDIT : Added 2nd Query for Book Content.

edited Jun 11 '17 at 07:43

answered Jun 10 '17 at 11:10

Santosh

393
2
11

added totalLoans, totalReturn. Make sure it's perfoming more operation in single query so it will be lengthy and time consuming. – Santosh Jun 10 '17 at 13:10
I am still testing. What is the "THEN date END" method? – Jun 10 '17 at 13:48
THEN an END is keyword and "date" is your column name to display date – Santosh Jun 10 '17 at 13:51
I see. I tested it yesterday a lot, and it seems it works, huge thanks for you and everyone! But one more thing, I update the post. – Jun 11 '17 at 07:08
Yep this is what I tried but it seems my MySQL getting timeout everytime. Stuck in "Loading" page and I must restart it. Anyways I am working with ~300.000 records. – Jun 11 '17 at 08:11
I am using PHPMyadmin. I navigate to SQL and paste my code and click Go. And it showing the yellow text "Loading" and stucks there. – Jun 11 '17 at 08:37
Ref this post : https://stackoverflow.com/a/21641325/5541139, is it same ? – Santosh Jun 11 '17 at 08:39
I tested the code out with ~30 records and it works. So the execution time is too much. Since its not my own server, i can not change any settings. – Jun 11 '17 at 08:46
But can you please show me how can i use parent query variable in child join select? – Jun 11 '17 at 09:17

score 0 · Answer 3 · answered Jun 10 '17 at 11:11

Here is my take at it. Basically the same as scaisEdge, but a bit more detailed. I created an sqlfiddle for it.

select
  i.`bookId`,
  i.`status`,
  i.`title`,
  group_concat(w.`writer`) as writers,
  coalesce(max(ll.`date`), '') as lastLoan,
  coalesce(max(lr.`date`), '') as lastReturn
from
  book_info i
left join
  book_writers w on w.bookId=i.bookId
left join
  book_log ll on (ll.bookId=i.bookId) and (ll.`action`='loan')
left join
  book_log lr on (lr.bookId=i.bookId) and (lr.`action`='return') and (lr.`date` >= ll.`date`)
group by
  w.bookId

It works well, but can you please add another field like "totalLoans" which counts how many loans has the books? I updated the fiddle: http://sqlfiddle.com/#!9/6aca38/3 And it has also duplicated columns at the "owners" if I add more records in logs — , Jun 10 '17 at 12:24

MYSQL multiple select from multiple tables

3 Answers3