Get a row from one table if all its child table rows matched with the search criteria?

Question

Suppose I have Table 1 and Table 2.

Table1 has (pkcol, Seq) as columns. Table2(pkcol, fkcol_from_Table1, details)

Ex:

Table1

pkcol	seq
10	1
20	2
30	7
.
.

Table2

pkcol	fkcol_from_Table1	details
100	10	R1
101	10	R2
103	20	R1
104	30	R4
105	30	R2

Suppose my search criteria are R1 and R2, Then I want to get the details from Table 1 by joining Table 2. From the above example with the search criteria get only 100 and 101(pkcol) from Table 2 and using the foreign key join get "1" from Table 1.

Can I do this using a query in oracle sql?

Does this answer your question? [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) — astentx, Apr 19 '23 at 10:00

score 0 · Answer 1 · answered Apr 19 '23 at 10:05

select t2.*, t1.seq
from table2 t2 left join table1 t1 on t2.pkcol=t1.pkcol

I understand the above would be the way you wish to join the tables, considering you must have all there is in table2. To that query you may add in the where clause the filter conditions you need. Still, it's not clear to me what those tables are meant to represent. You should have provided the DDL for both tables, including also the PK and FK. Is it a many to many relation? Which is the table that has a primary key referred by the other, which will have several rows referring the same PK? A one to none or one relation wouldn't seem to me like good database design. I'd rather have only one table if there is not to be a one to many relation.

score 0 · Answer 2 · answered Apr 19 '23 at 12:07

If I get it right you want rows containing both 'R1' and 'R2' for the same FK_COL referencing to table 1 PK_COL.

WITH        --  Sample data
    tbl_1 (PK_COL, SEQ) AS
        (   Select 10, 1 From Dual Union All
            Select 20, 2 From Dual Union All
            Select 30, 7 From Dual          
            
        ),
    tbl_2 (PK_COL, FK_COL, DETAILS) AS
        (   Select 100, 10, 'R1' From Dual Union All
            Select 101, 10, 'R2' From Dual Union All
            Select 103, 20, 'R1' From Dual Union All
            Select 104, 30, 'R4' From Dual Union All
            Select 105, 30, 'R2' From Dual 
        )
--
--   Main SQL    
Select  PK_COL, FK_COL, DETAILS, SEQ
From    ( Select    t2.PK_COL, t2.FK_COL, t2.DETAILS, t1.SEQ,
                  LISTAGG(DISTINCT t2.DETAILS, ', ') WITHIN GROUP (Order By t2.DETAILS) Over(Partition By t1.PK_COL) "LIST_DETAILS"
          From  tbl_1 t1
          Inner Join tbl_2 t2 ON(t2.FK_COL = t1.PK_COL)
        )
Where LIST_DETAILS = 'R1, R2'
--
-- R e s u l t :
    PK_COL     FK_COL DETAILS        SEQ
---------- ---------- ------- ----------
       100         10 R1               1
       101         10 R2               1

Get a row from one table if all its child table rows matched with the search criteria?

2 Answers2