How to compare multiple columns in MySql and display result

Question

I have two table. I want to compare two tables gift and show the result. But I don't want to show N/A value. and I want to show result matching value and not matching value. if table1 gift is match the table2 gift and table1 user_it is match the table2 user_id then show status yes. otherwise status show no and also count the gift quantity.
Table1

| id  | user id | name                 | gift1      | gift2 | gift3      | gift4  |
| --- | ------- | -------------------- | ---------- | ----- | ---------- | ------ |
| 1   | 511     | Md. Amir Hossain     | N/A        | Pad   | Mobile     | Laptop |
| 2   | 522     | Md. Faruk Khan       | Mobile     | Book  | Pen        | N/A    |
| 3   | 533     | Md. Rifat            | Book       | N/A   | Laptop     | Watch  |
| 4   | 544     | Md. Hossain Ali      | Laptop     | N/A   | N/A        | N/A    |
| 6   | 559     | Md. Milon khan       | Watch      | Pen   | N/A        | N/A    |
| 7   | 559     | Md. Rifat            | Mobile     | Pen   | Watch      | Book   |
| 8   | 522     | Md. Faruk Khan       | Pen        | N/A   | N/A        | Mobile |

Table2

| id  | user id     |       name        | gift name   |
| ----| ------------| ----------------- | ----------- |
| 1   | 511         | Md. Amir Hossain  | Watch       |
| 3   | 533         | Md. Rifat         | Watch       |
| 9   | 544         | Md. Hossain Ali   | Laptop      |
| 10  | 522         | Md. Faruk Khan    | Pen         |

I want to show like this:

Result

|          name     | gift        | qty | status|
| ----------------- | ----------- | --- | ------|
| Md. Amir Hossain  | Pad         | 1   | No    |
| Md. Amir Hossain  | Mobile      | 1   | No    |
| Md. Amir Hossain  | Laptop      | 1   | No    |
| Md. Faruk Khan    | Mobile      | 2   | No    |
| Md. Faruk Khan    | Book        | 1   | No    |
| Md. Faruk Khan    | Pen         | 2   | Yes   |
| Md. Rifat         | Book        | 2   | No    |
| Md. Rifat         | Laptop      | 1   | No    |
| Md. Rifat         | Watch       | 2   | Yes   |
| Md. Rifat         | Pen         | 1   | No    |
| Md. Hossain Ali   | Laptop      | 1   | Yes   |
| Md. Faruk Khan    | Pen         | 1   | Yes   |
| Md. Faruk Khan    | Mobile      | 1   | No    |

Answer 1

You can find an effective solution to this problem by splitting it into three simpler sub-steps:

1. aggregate the four gift<k> columns contained in the Table1, using the UNION clause on the same table.

2. extract the qty field by using an aggregation function like COUNT while removing NULL values.

3. extract the status field by joining with the Table2 and wrangling the TABLE2.gift_name field using a conditional statement like IF or CASE.

Each step should work on the resulting table of the previous steps in a nested fashion.

Hope that helps.