0

So, say you are working with a field rowid in the table rcheck under database fildb, which is a primary key. So here is the field rowid, with the records 9800000...9800003:

rowid

9800000

9800001

9800002

9800003

I want to do inner join to the field rfile_rowids from the table rfile under database fildb, which has 2 values in each record separated by comma because I am trying to match records. Here is rfile_rowids with the 2 values separated by commas:

rfile_rowids

0,9800000

0,9800001

0,9810000

0,9820000

So far, I did:

SELECT

rc.rowid,rf.rfile_rowids

FROM fildb.rcheck rc

INNER JOIN fildb.rfile rf

ON rc.rowid LIKE SUBSTRING(rf.rfile_rowids,1,9);

However, my program keeps running and won't stop running, and I have to stop executing it. So my question is how do you inner join a table that has 1 value per row to another table that has 2 values per a row separated by a comma in MySQL?

Shadow
  • 33,525
  • 10
  • 51
  • 64
user749068
  • 1
  • have you tried without like clause? For ex INNER JOIN fildb.rfile rf ON rc.rowid = SUBSTRING(rf.rfile_rowids,3,9) . How ever, calculating substring for each row itself is a heavy operation but time will depend on the number of rows. – Amit Verma Jul 28 '21 at 02:21
  • Are you trying to join on the second number after the comma? – Alex Sapozhnikov Jul 28 '21 at 02:21
  • yes, and also want to understand the exact problem. So, there can be many solutions. Again it depends on number of rows. – Amit Verma Jul 28 '21 at 02:24
  • Well if you are trying to join on the second number you should try changing your join code to "rc.rowid = SUBSTRING(rf.rfile_rowids,3,9)" – Alex Sapozhnikov Jul 28 '21 at 02:40
  • @AlexSapozhnikov, yes I want to match the numbers in the field rowid to the second numbers after the comma in the field rfile_rowids – user749068 Jul 28 '21 at 02:43
  • Thank you. I will try to do INNER JOIN fildb.rfile rf ON rc.rowid = SUBSTRING(rf.rfile_rowids,3,9), and see if I get any matching records – user749068 Jul 28 '21 at 02:44
  • No joins will be able to use indexes and therefore will be slow. – Shadow Jul 28 '21 at 02:50

0 Answers0