MySQL select two rows from same table

Question

I have two tables, company and openhours.

company id | name

openhours id | day | company | open | close

openhours has 7 rows per company, with each days open hours ('mon', 'tue', 'wed' etc)

Basically I want to fetch the openhours for both wednesday (wed) and thursday (thu)

SELECT * FROM openhours WHERE company=1 AND day='wed'
SELECT * FROM openhours WHERE company=1 AND day='thu'

What I'm looking for though is getting both the company and openhours for the company in the same query, but so far I can only get 1 row for openhours

SELECT company.*, openhours.* FROM company INNER JOIN openhours ON openhours.company=company.id WHERE openhours.day == 'wed' OR openhours.day == 'thu

Any help? Been sitting on this for quite some time now

EDIT This is how the openhours table looks like (company id 1)

id | day | company | open | close

1 mon 1 08:00 16:00
2 tue 1 08:00 16:00
3 wed 1 10:00 16:00
4 thu 1 11:00 15:00
5 fri 1 08:00 16:00
6 sat 1 11:00 14:00
7 sun 1 11:00 14:00

You mentioned the query only gives you one record, what are you using to look at the results of your query? — Adam Wenger
– Adam Wenger, Commented Jan 9, 2014 at 21:20

Sam · Accepted Answer · 2014-01-09 21:37:32Z

2

SELECT
  company.*, openhours.*
FROM
  company
  INNER JOIN
    openhours ON openhours.company=company.id
WHERE
  company.id = 1 AND
  openhours.day IN ('wed', 'thu')
;

You say you still get 1 row, so I think there is some other issue because it worked fine for me in this SQLFiddle. How are you calling this query? There may be a problem in your execution or environment. You could also try a LEFT JOIN, but this should be INNER.

edited Jan 9, 2014 at 21:37

answered Jan 9, 2014 at 21:14

Sam

20.5k2 gold badges48 silver badges71 bronze badges

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

5 Comments

Emil Over a year ago

Only gives me 1 record for openhours which is for wednesday. I'm looking to get both for wednesday and thursday. Thank you for your input though, really appreciate it!

Emil Over a year ago

Same issue, only gives me the record for wednesday (wed)

Sam Over a year ago

Weird. Can you try changing company.id to openhours.company?

Emil Over a year ago

Same problem. But isn't it bound to only return 1 record from the openhours table, whichever is first (which is wednesday in this case).

Sam Over a year ago

@Emil please view my SQLFiddle, everything should be working fine.

Claas Wilke · Accepted Answer · 2014-01-09 21:19:57Z

1

SELECT company.*, openhours.* 
FROM company INNER JOIN openhours ON openhours.company=company.id 
WHERE (day='wed' OR day='thu')

edited Jan 9, 2014 at 21:19

answered Jan 9, 2014 at 21:13

Claas Wilke

1,9911 gold badge18 silver badges29 bronze badges

5 Comments

Emil Over a year ago

Only gives me 1 record for openhours which is for wednesday.

Claas Wilke Over a year ago

Can you please post the data of your database query for selecting all opening hours for your company. Are you sure that there are opnening hours for Thursday in your DB?

Emil Over a year ago

Yes I'm sure there are opening hours for thursday. Your query is bound to only return one record from the openhours table, whichever is first (which is wednesday in this case)

Claas Wilke Over a year ago

I dont think so as there is no LIMIT at the and of the query.

Emil Over a year ago

I've tried every single modification of this as it is but always return the first match (wednesday). Try for yourself if you like. Been stuck on this for 2 days now

elbuild · Accepted Answer · 2014-01-09 21:16:12Z

0

What about:

SELECT * FROM openhours WHERE company=1 AND (day='wed' OR day='thu');

or:

SELECT company.*, openhours.* FROM company INNER JOIN openhours ON openhours.company = company.id WHERE openhours.day IN ('wed', 'thu')

Your query had a '==' (syntax error).

answered Jan 9, 2014 at 21:16

elbuild

4,8894 gold badges26 silver badges32 bronze badges

1 Comment

Emil Over a year ago

Same as the answers above. Feels like I've tried everything. What I need is to get both the record for wednesday (wed) and thursday (thu)

D. Rattansingh · Accepted Answer · 2014-01-09 21:58:23Z

0

Select *
from company c, openhours o
where c.id=o.company and c.id=1 and o.day='wed' or o.day='thu';

answered Jan 9, 2014 at 21:58

D. Rattansingh

1,6793 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL select two rows from same table

4 Answers 4

5 Comments

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related