CODEWITHSUNDEEP
sqlqgisspatialite
MJM
MJM

Reputation: 123

SQL many-to-one join using two foreign keys

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

Upvotes: -1

Views: 149

Answers (1)

shawnt00
shawnt00

Reputation: 17935

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.

Upvotes: 0

Related Questions