Home Mysql - Fetch row from join table based on the value from main table
Reply: 0

Mysql - Fetch row from join table based on the value from main table

user1750
1#
user1750 Published in July 21, 2018, 1:49 pm

I have two tables in a MySQL database like this:

Service:

service_id |userid      | service_name | pending  | accepted|completed 
-----------+------------+--------------+----------+---------+--------
1          | 1          |  Spa         | 1        |0        |0
2          | 2          |  Cleaning    | 0        |1        |0
3          | 3          |  General     | 1        |0        |0

request_status: (id is primary key)

id  |  service_id   | Userid |  status      | accepted_date 
----+---------------+--------+--------------+---------------
1   |  1            |  1     |  Pending     | 2018/03/02
2   |  2            |  2     |  Pending     | 2018/03/02
3   |  2            |  2     |  Accepted    | 2018/03/05
4   |  3            |  3     |  Pending     | 2018/03/06

OUTPUT

service_id   | service_name | userid|  Status    | Date
-----------+----------------+-------+------------+------------+---------
1          | Spa            | 1     |  Pending   | 2018/03/02
2          | Cleaning       | 2     |  Accepted  | 2018/03/05
3          | General        | 3     |  Pending   | 2018/03/06

For each Service user opt, the status will be update in another table request status.

I want the output with joining 2 tables with for each service its status. Eg. For the service id 2, its been accepted, so it has 2 entries in the second table. I tried with join but its fetching both pending and accepted for the service 2 which should fetch only one.

SELECT a.*, b.* FROM service a left outer join request_statusb on a.service_id =b.service_id

How to do that.? any help Please

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.311655 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO