0

I wanted to add a duplicate record by basing on the count from another table

For example:

In table #1, I have the following records:

+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+

And in table #2, I have the following records.

+------------------------+
| first_name | last_name | 
+------------------------+
| John       | Smith     |
| John       | Smith     |
| Jane       | Doe       |
| Jane       | Doe       |
| Rock       | Lee       |
+------------------------+

And since there 2 records for John Smith and Jane Doe in table #2, my expected output for table #1 is like this:

+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+
0

You could JOIN the tables eg

select T1.first_name, T1.last_name, T1.role
from table#1 T1
  join table#2 T2
    on T1.first_name = T2.first_name and T1.last_name = T2.last_name
;

-- result
+----------+---------+------+
|FIRST_NAME|LAST_NAME|ROLE  |
+----------+---------+------+
|John      |Smith    |Dev   |
|John      |Smith    |Dev   |
|Jane      |Doe      |Tester|
|Jane      |Doe      |Tester|
|Rock      |Lee      |Dev   |
+----------+---------+------+

DBfiddle here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.