Merging Multiple data with Condition mysql

Question

I have a database like this

ID | booking_id | branch_id | service_id | staff_id | rating
1  |    21      |    2      |   null     |   null   |   5
2  |    21      |    null   |    5       |   null   |   3
3  |    21      |    null   |    null    |    7     |   5
4  |    22      |    3      |   null     |   null   |   4
5  |    22      |    null   |    8       |   null   |   2
6  |    22      |    null   |    null    |    10    |   1

Is there a way where i can extract merge the data with the same booking_id and the result will be like this

booking_id |branch name  |service name| staff name |branch rating|service rating|staff rating 
    21     |LA branch    |   massage  |   John     |  5          |      3       | 5
    22     |Vegas Branch |   therapy  |   May      |  4          |      2       | 1

Looks like you have updated the question. I have answered according to getting the ids. — Mushif Ali Nawaz
– Mushif Ali Nawaz, Commented Feb 15, 2021 at 6:17

Mushif Ali Nawaz · Accepted Answer · 2021-02-15 06:25:52Z

2

You can use MAX() function to get the non null value for a given booking_id. Something like this:

SELECT booking_id,
       MAX(branch_id)                                AS branch_id,
       MAX(service_id)                               AS service_id,
       MAX(staff_id)                                 AS staff_id,
       MAX(IF(branch_id IS NOT NULL, rating, NULL))  AS branch_rating,
       MAX(IF(service_id IS NOT NULL, rating, NULL)) AS service_rating,
       MAX(IF(staff_id IS NOT NULL, rating, NULL))   AS staff_rating
FROM booking_rating # assuming the table name
GROUP BY booking_id;

Now that you have updated your question, it nullifies my previous answer. This is how the updated query would look like:

SELECT booking_id,
       branch.name  AS branch_name,
       service.name AS service_name,
       staff.name   AS staff_name,
       branch_rating,
       service_rating,
       staff_rating
FROM (
         SELECT booking_id,
                MAX(branch_id)                                AS branch_id,
                MAX(service_id)                               AS service_id,
                MAX(staff_id)                                 AS staff_id,
                MAX(IF(branch_id IS NOT NULL, rating, NULL))  AS branch_rating,
                MAX(IF(service_id IS NOT NULL, rating, NULL)) AS service_rating,
                MAX(IF(staff_id IS NOT NULL, rating, NULL))   AS staff_rating
         FROM booking_rating # assuming the table name
         GROUP BY booking_id
     ) AS booking_derived
         # assuming "branch", "service", "staff" as table names.
         JOIN branch ON branch.id = booking_derived.branch_id
         JOIN service ON service.id = booking_derived.service_id
         JOIN staff ON staff.id = booking_derived.staff_id;

Note: I have assumed the table names because this information is missing in question. You would have to replace the table names according to your schema.

edited Feb 15, 2021 at 6:25

answered Feb 15, 2021 at 6:10

Mushif Ali Nawaz

3,9163 gold badges21 silver badges33 bronze badges

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

4 Comments

Akina Over a year ago

Now nowhere. Initially - in IF().

Mushif Ali Nawaz Over a year ago

@Akina Oh yes, I missed adding the 3rd parameter. Then I realised it was missing. Thanks for pointing out.

Akina Over a year ago

That's why I prefer CASE - it allows to skip ELSE part without problems.

Mushif Ali Nawaz Over a year ago

Yes, you're right. However, I prefer CASE when there are more than one IF conditions.

Akina · Accepted Answer · 2021-02-15 05:43:09Z

1

SELECT booking_id,
       MAX(branch_id) branch_id,
       ...
       MAX(CASE WHEN branch_id IS NOT NULL THEN rating END) branch_rating,
       ...
FROM src_table
GROUP BY booking_id

answered Feb 15, 2021 at 5:43

Akina

43.2k6 gold badges17 silver badges30 bronze badges

2 Comments

dac dac Over a year ago

it works thanks . can i use this query to also fetch the names of each id, instead of staff_id it will return a staff name: "John Doe"

Akina Over a year ago

@dacdac Use this query as subquery, join (id-name) table(s) to it.

Collectives™ on Stack Overflow

Merging Multiple data with Condition mysql

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related