SQL many-to-one join using two foreign keys

Question

I have two tables (Table A & Table B) that I have in a database (SpatiaLite database). I would like to join all the entries in Table A with Table B using two foreign keys (TableA.Location & TableB.LSD, TableA.LICENCE_NO & TableB.Licence_No); however, there will be multiple INCIDEN_NO entries in Table A that match up with the joined rows in Table B.

Since there will be many INCIDEN_NO entries associated with the Licence_No in Table B, I would like to evenly distribute the INCIDEN_NO entries among all the LIC_LI_NO entries in Table B that align with the foreign keys. The rows from Table A can be randomly assigned to each LIC_LI_NO in Table B randomly and in no particular order.

I cannot seem to find a SQL expression for this operation, which has really stumped me for weeks.

picture of the join and potential output table

Please provide your data as tabular text. This tiny image is unreadable. — GMB, Sep 04 '20 at 21:43
From your data is not clear which table is the parent one and which one is the children table. The keys seems to be repeated in both tables; therefore, they are not "keys". — The Impaler, Sep 04 '20 at 21:46
Sorry about the image, however, if you right click the picture and open in a new window one can zoom in on it to make it readable. I am unsure about the parent table and child table question. I just thought that columns with unique values to that row would be the key. These tables are attribute tables imported into a SpatiaLite database called Pipe_inc.sqlite. I am very new to databases and SQL. — MJM, Sep 04 '20 at 22:23
What is the purpose of randomizing the license information? Is this about creating test data or something? Is this not good enough as test data already? — shawnt00, Sep 04 '20 at 22:25
The Licence_No is for a whole pipeline, but the LIC_LI_NO is for each segment of the pipeline. The data in Table A represents incidents (INCIDEN_NO) for each LIC_LI_NO over time, but the LIC_LI_NO for each INCIDEN_NO is not correct for the segment at each grid square location (LOCATION). Table B is the midpoint of each pipeline in each grid square (400 m x 400 m). The Licence_No for the pipeline(s) in each grid sq is correct but there are potentially an n-number of pipelines with the same Licence_No. I want to assign the incidents in Table A randomly among the midpoints of each LIC_LI_NO. — MJM, Sep 05 '20 at 16:38

shawnt00 · Accepted Answer · 2020-09-04T23:18:44.627

You could match the rows up randomly with something like this:

with B as (
    select row_number() over () as rn, lic_li_no
    from B
), A as (
    select abs(random()) % cntb + 1 as randnum, a.*
    from A cross apply (select count(*) as cntb from B) b
)
select *
from A inner join B on A.randnum = B.rn;

You could also generate the cross product and keep random rows. I tried this query out on SQLite but it didn't seem to work as I expected:

select * from A cross join B where abs(random()) % 20 = 1

That said, I don't understand the purpose behind all this and it's certainly not a common thing to do in a database.

SQL many-to-one join using two foreign keys

1 Answers1