MySQL return multiple rows from joined table as columns

Question

I have 2 tables (shop and opening hours).

I wish to return the shop opening hours as columns rather than separate rows. I've seen a few ideas on Stack but none that I can transpose to my own situation.

Stores
------
id
name

Opening Hours
-------------
store_id
dayofweek
openinghh
openingmm
closinghh
closingmm

Each store will have 7 entries in the Opening Hours table (one for each day of the week)

The returned row will be

store_id | name | Monday Opening | Monday Closing |Tues Opening | Tues Closing etc

Mr.P · Accepted Answer · 2014-12-16 10:17:08Z

2

not so hard :)

SELECT s.id as store_id
, s.name as store_name
##
, concat(oh_mon.openinghh, ':', oh_mon.openingmm) as monday_opening
, concat(oh_mon.closeinghh, ':', oh_mon.closeingmm) as monday_closing
##
, concat(oh_tue.openinghh, ':', oh_tue.openingmm) as tuesday_opening
, concat(oh_tue.closeinghh, ':', oh_tue.closeingmm) as tuesday_closing
##
, concat(oh_wed.openinghh, ':', oh_wed.openingmm) as wednesday_opening
, concat(oh_wed.closeinghh, ':', oh_wed.closeingmm) as wednesday_closing
##
FROM stores s
##
LEFT JOIN opening_hours oh_mon
ON oh_mon.store_id = s.id
AND oh_mon.dayofweek = 'Monday'
##
LEFT JOIN opening_hours oh_tue
ON oh_tue.store_id = s.id
AND oh_tue.dayofweek = 'Tuesday'
##
LEFT JOIN opening_hours oh_wed
ON oh_wed.store_id = s.id
AND oh_wed.dayofweek = 'Wednesday'
##
WHERE 1=1

and use LEFT JOIN for each day you want to be shown

answered Dec 16, 2014 at 10:17

Mr.P

1,2674 gold badges26 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeepstone Over a year ago

Excellent. I did wonder if there was a simpler way. Thank you!

Mr.P Over a year ago

yes but it depends on your data model ... could you send me an example of your data?

Collectives™ on Stack Overflow

MySQL return multiple rows from joined table as columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related