sql - Displaying records in table after join and group by a particular column -

sql - Displaying records in table after join and group by a particular column -

i have 2 tables contain info :

table1 : abc

||entity_id| entity_title| cust_code| cust_acc_no || ---------------------------------------------------------- || ab101z | ind | 101z | 1234 || || ab101z | pak | 101z | 1357 || || cd101y | ind | 101y | 2468 || || ef101x | ind | 101x | 3579 || || jk201n | lko | 201n | 5678 ||

table2 : def

||entity_title| in_count| out_quant|| --------------------------------------------- || ind | 10 | 7 || || lko | 7 | 7 || || pak | 5 | 2 ||

joined table : abcdef

||entity_id| entity_title| cust_code || -------------------------------------------------- || ab101z | indpak | 101z || || cd101y | indpak | 101y || || ef101x | indpak | 101x ||

i want bring together tables abc , def resultant in table abcdef.

while joining both tables , records grouped entity_title. joining status such in_count!=out_count. example, in such situation lko entity_title not part of resultant table.

i need replace entity_title records matching status 3rd record signifying matched record, ex, indpak replacement records whether ind , pak both or either of two.

i tried come solution not able form single query.thanks in advance solution suggested.

this solution avoids hard-coding. comprises 3 steps:

the first sub-query identifies mutual (entity_id, cust_code) combinations entity_titles different counts.

the sec sub-query identifies entity_titles own combos , derives compound entity_title them. (it uses listagg, 11gr2 thing, there workarounds string concatenation in before versions of database).

the outer query produces required output, substituting compound entity_title original entity_title.

here whole thing. admit don't reliance on distinct clauses required output joining rules produce unwanted duplicates.

with prs ( select abc.entity_id , abc.cust_code abc bring together def on abc.entity_title = def.entity_title def.in_count != def.out_quant grouping abc.entity_id, abc.cust_code having count(*) > 1 ) , ent ( select distinct abc.entity_title , listagg(abc.entity_title) within grouping (order abc.entity_title) on (partition prs.entity_id, prs.cust_code) cmp_entity_title abc bring together prs on abc.entity_id = prs.entity_id , abc.cust_code = prs.cust_code ) select distinct abc.entity_id , ent.cmp_entity_title , abc.cust_code ent bring together abc on abc.entity_title = ent.entity_title order abc.entity_id , abc.cust_code /

please note output strongly dependent on initial status of data. if @ inevitable sql fiddle, see have included additional rows set-up.

the first commented out abc record ...

/* insert abc values ('ab101z','ban','101z', 5151 ); */

.. creates matched triple, banindpak, replaces occurrences of ban, ind or pak. logical outcome of rules , 1 assume expect.

the other commented out abc record ...

/* insert abc values ('jk101x','tib','101k', 3434 ); */

... creates sec matched pair, paktib, presence generates multiple results pak entity's records. logical outcome of rules perhaps less-expected one.

sql database oracle join group-by