join two tables in sql using common column

Question

I have two table.

tablea contains assetID, branchID, latID, lonID. Each row is unique.

assetID, branchID, latID, lonID

For every assetID in tablea, there are 32 entries in tableb in the following format:

assetID, branchID, risk1, risk2, risk3, risk4

I want to randomly select 10 rows from tablea, pull the data from tableb for these random assetID and join them together to get the table in following format

assetID, branchID, latID, lonID, risk1, risk2, risk3, risk4

So far I have the below sql query but I am unable to join the two tables:

select * from tableb where branchID <2 and assetID in
(select top 10 assetID from tablea where assetID is not null and branchID <2)

Answer 1

Does this solve your problem?

select * from (
   select * from  /*i get the first 10 rows from tablea*/
       tablea
   where  branchID < 2
   limit 10
) as tablea
join tableb /*i pull the relative data from tableb */
on tablea.assetID = tableb.assetID and tableb.branchID < 2