What I have: The next structure:
- LID (PRIMARY with auto increment)
- LOCUS (varchar (9)) Example value: (At1g30500) (Fixed Number of LOCUS values =173 rows)
- StartPoint (INT) (Exclusive value assigned by locus)
EndPoint (INT) (Exclusive value assigned by locus)Actual table looks like the following
| LID | LOCUS | StartPoint | EndPoint| 1 AT1G30500 734 273 2 AT3G15690 11415 8848 3 AT3G05690 262900 195300 . . . . 173 At5g01410 366700 264418
- idDMRs (Primary with auto increment)
- StartPos (INT)(Exclusive value assigned by idDMRs)
- EndPos (INT) (Exclusive value assigned by idDMRs)
| idDMRs | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n 1 1003 716 . . . . . . . . . 156 197126 195371 . . . 172 197299 196119 . . . 201 197233 195621 . . . . . . . . . 20633 262857 262857 . . .
What I want : Select all values from table DMRs that fulfill the next condition,
EndPos >= EndPoint AND StartPos <= StartPoint
This for every LOCUS in table ChrM (Total of 173 times) to get an (LOCUS, idDMRs, EndPos, StartPos, OTHER1, OTHer2, OTHER n) array.
As a newbie to Mysql I tried doing this one by one printing Table ChrM in paper and inserting LOCUS respective StartPoint and EndPoint by hand and querying individually LOCUS by LOCUS in the following manner:
SELECT * FROM DMRs WHERE EndPos >= 195300 AND StartPos <= 262900;
And the results are what I want, but I wonder if I will ever finish to analyze all my data when I have 50 different DMRs tables to analyze doing this 173 times for table.Example of my query output
This is the result I want to get for every LOCUS
| idDMRs | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n 156 197126 195371 . . . 172 197299 195419 . . . 201 197233 195621 . . .
| LOCUS | idDMRs | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n AT3G05690 156 197126 195371 . . . AT3G05690 172 197299 195419 . . . AT3G05690 201 197233 195621 . . .
So, in conclusion I would like help on how to resolve this problem or any advice on what I should read or know in order to resolve this on my own.