How to pull 'one to many' relationship?

Richard Conaway 0
Viewed 17

Postgres using dBeaver or SQLDeveloper.

I need help with the logic on this.

I have the following data

rule_id primary_component secondary_component
500430 R0558
500430 R0477
500430 Q6457
500430 Q2501
500430 Q2502
500430 Q2503

And I need to pull the data in this manner:

rule_id primary_component secondary_component
500430 R0558 Q2501
500430 R0558 Q2502
500430 R0558 Q2503
500430 R0477 Q2501
500430 R0477 Q2502
500430 R0477 Q2503
500430 Q6457 Q2501
500430 Q6457 Q2502
500430 Q6457 Q2503

I can't seem to manage it with the GROUP BY function.

oracle-sqldeveloper sql postgresql
1 Answers

use join with filtered subqueries:

select t1.rule_id, primary_component, secondary_component 
from (
    select rule_id, primary_component
    from tablename 
    where secondary_component is null
) t1 
join (
    select rule_id , secondary_component
    from tablename 
    where primary_component is null
) t2 on t1. rule_id = t2. rule_id
eshirvana0
Related