CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Suika
Suika

Reputation: 660

MySQL: Using left join and union in one query

I have two tables named tblborrow and tblreturn wherein they both have date columns.

Now, what I am trying to do is to display almost all my columns from both tblborrow and tblreturn, in order by their date_borrowed and date_returned in union.

My tblborrow contains the following columns:

While tblreturn contains:

I have done displaying them with left join using this:

SELECT a.isbn, a.member_id, a.staff_id, a.date_borrowed, b.staff_id, b.date_returned
FROM tblborrow AS a left join tblreturn AS b on a.borrow_id = b.borrow_id
ORDER BY date_borrowed, date_returned

It has given me what I need except the date_returned and date_borrowed are displayed in separate columns, of course.

Now I tried to use UNION on them by testing if it also gives me what I need and so I used:

SELECT date_borrowed FROM tblborrow
UNION ALL
SELECT date_returned FROM returning

And it has indeed. But I need these to be in one query only. I tried to simply combine the queries like this:

SELECT a.isbn, a.member_id, a.staff_id, b.staff_id,
(SELECT date_borrowed FROM tblborrow
UNION ALL
SELECT date_returned FROM returning)
FROM tblborrow AS a left join tblreturn AS b on a.borrow_id = b.borrow_id
ORDER BY date_borrowed, date_returned

But it says "Subquery returns more than 1 row" and I still can't seem to do it after lots of thorough Google-ing.

Help, anyone? Thanks in advance!

Upvotes: 0

Views: 20046

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try the below. Because you are merging 2 different date values into the same column you will want to distinguish between which rows reflect a return vs. borrow action.

I am assuming that you want borrow/return date detail to be on every row. If you don't care about that then the last response by Gordon would be fine.

The first block below will show blanks for books borrowed but not returned.

Also UNION ALL will simply retain duplicate lines versus discarding them. The query below should function no differently if UNION ALL were used rather than UNION because it is not going to return any duplicates.

select          'BORROW'             as action
                , b.date_borrowed    as action_date
                , b.isbn
                , b.member_id
                , b.staff_id         as borrow_from_staff
                , b.date_borrowed
                , r.date_returned
                , r.staff_id         as return_to_staff
from             tblborrow b
      left join tblreturn r
             on b.borrow_id = r.borrow_id
union
select          'RETURN'             as action
                , r.date_returned    as action_date
                , b.isbn
                , b.member_id
                , b.staff_id         as borrow_from_staff
                , b.date_borrowed
                , r.date_returned
                , r.staff_id         as return_to_staff
from            tblreturn r
           join tblborrow b
             on r.borrow_id = b.borrow_id
order by        action_date
                , isbn

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Try this by adding a join on your union set 

SELECT 
  a.isbn,
  a.member_id,
  a.staff_id,
  b.staff_id,
  u.combined_date 
FROM
  tblborrow AS a 
  LEFT JOIN tblreturn AS b 
    ON a.borrow_id = b.borrow_id 
  LEFT JOIN 
    (SELECT 
      borrow_id,
      date_borrowed  AS combined_date
    FROM
      tblborrow 
    UNION
    ALL 
    SELECT 
      borrow_id,
      date_returned  AS combined_date
    FROM
      returning) u 
    ON (u.borrow_id = a.borrow_id) 
ORDER BY u.combined_date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

If you want to separate rows for a borrow/return combination, then union all is appropriate. If so, I think this does what you want:

SELECT b.isbn, b.member_id, b.staff_id, date_borrowed, 'borrow' as which
FROM tblborrow b
UNION ALL
SELECT b.isbn, b.member_id, r.staff_id, date_returned, 'return' as which
FROM tblreturn r join
     tblborrow b
     on r.borrw_id = b.borrow_id;

Upvotes: 3

Related Questions