...

SQL, output associated records having many-to-many relationship row by row

 

Two database tables – Primary and Secondary – have same structure, where W, H and D are primary key fields.

Primary table:

Name

W

H

D

Primary item 1

100

500

300

Primary item 2

100

600

300

Primary item 3

200

500

300

Primary item 4

100

500

300

Primary item 5

100

600

300

Primary item 6

200

500

300

Secondary table:

Name

W

H

D

Secondary item 1

100

500

300

Secondary item 2

100

600

300

Secondary item 3

200

500

300

Secondary item 4

100

500

300

Secondary item 5

100

600

300

Secondary item 6

200

500

300

Task: Find records meeting the condition W=100, H=500, and D=300 from both tables, and output the eligible record in Primary table first and then each related record in Secondary table. Below is the expected result:

Name

W

H

D

Primary item 1

100

500

300

Secondary item 1

100

500

300

Secondary item 4

100

500

300

Primary item 4

100

500

300

Secondary item 1

100

500

300

Secondary item 4

100

500

300

Write the following SPL code:

A

1

=sqlServer1.query("select * from Primary where W = 100 and H = 500 and D = 300")

2

=sqlServer1.query("select * from Secondary where W = 100 and H = 500 and D = 300")

3

=if(A2,A1.conj(~|A2))

A1, A2: Run simple SQL statements to perform conditional queries.

A3: Return null if A2 is empty; and if A2 isn’t empty, loop through each record of A1 (represented by ~), concatenate ~ and A2, and then concatenate results of all rounds of loops.

Source:https://stackoverflow.com/questions/78435969/sql-join-two-tables-as-many-times-as-there-are-matches-in-the-first-table


 
 •  78 View  •