Combining simple QUERIES in MySQL

Question

I have a table that looks as follows service:

id | service           | status | multicall | date_created         |
--------------------------------------------------------------------
10 | Cleaning          |    1   |      0    |  2016-09-20 19:11:01 |    
11 | Gardening         |    1   |      0    |  2016-09-20 19:12:07 |
12 | Dishes            |    1   |      1    |  2016-09-20 19:14:28 |    
13 | Take out rubbish  |    1   |      1    |  2016-09-20 19:14:28 |

This a a table that users can can select service from that they want someone to do. Once the service is finished, the user history gets saved in a different table user_service_hist:

id | user_id | service_id | date_created         |
--------------------------------------------------
1  |    1    |     10     |  2016-11-25 10:49:05
2  |    1    |     11     |  2016-11-27 23:58:46
3  |    1    |     12     |  2016-11-28 00:01:36
4  |    1    |     13     |  2016-11-28 12:07:17

The user can only select each service once EXCEPT the service marked with a multicall column value of 1.

When I call the list of available service I use the follwing query:

SELECT s.id, s.service, s.date
from service s WHERE not exists (Select 1 from user_service_hist ush    
where ush.service_id = s.id AND ush.user_id = 1)

This query selects zero rows as I the user has completed all services. I would like to display service nr. 12 and 13 always as these have a multicall column value of 1, regardless of whether it was completed or not. Could someone tell me how to modify my query?

Wouldn't a simple "OR s.multicall = 1" suffice?

hank
– hank

2016-12-03 13:55:54 +00:00
Commented Dec 3, 2016 at 13:55 — hank
– hank, Commented Dec 3, 2016 at 13:55
@Stefan I think hank is right. isn't?

StackUseR
– StackUseR

2016-12-03 13:59:40 +00:00
Commented Dec 3, 2016 at 13:59 — StackUseR
– StackUseR, Commented Dec 3, 2016 at 13:59

ScaisEdge · Accepted Answer · 2016-12-03 14:01:18Z

1

You could combine the query with an union for multicall

  SELECT s.id, s.service, s.date
  from service s WHERE not exists (Select 1 from user_service_hist ush    
  where ush.service_id = s.id AND ush.user_id = 1)
  union 
  SELECT s.id, s.service, s.date
  from service s WHERE multicall  = 1

answered Dec 3, 2016 at 14:01

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2016-12-03 14:00:49Z

0

You have two conditions for available services:

The user has not used the service previously.
The service is multi-call.

You just need to include both in the query. The second goes in the where clause as an additional condition:

select s.*
from service s 
where not exists (Select 1
                  from user_service_hist ush    
                  where ush.service_id = s.id and ush.user_id = 1
                 ) or
      s.multicall = 1;

answered Dec 3, 2016 at 14:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Stefan Over a year ago

Hi I think that this should work. Unfortunately I have other conditions in my first WHERE clause. I did not mention these to simplify the query. When I try to run the new query with the OR clause at the end my server runs into an inifinite loop and says "Loading...." in phpMyAdmin

Collectives™ on Stack Overflow

Combining simple QUERIES in MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related