Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What I have: The next structure:

Table ChrM

  • 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
    

Table DMRs

  • idDMRs (Primary with auto increment)
  • StartPos (INT)(Exclusive value assigned by idDMRs)
  • EndPos (INT) (Exclusive value assigned by idDMRs)
  • Other...
Actual table looks like this
        | 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
         |  idDMRs  | StartPos | EndPos | OTHER 1| OTHER 2 | OTHER n
             156      197126    195371      .       .         .
             172      197299    195419      .       .         .
             201      197233    195621      .       .         . 
This is the result I want to get for every LOCUS
        | 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.

share|improve this question
1  
You may want to post your actual table definitions in this question -- the narrative descriptions are hard to read -- and tag it for MySQL in addition to workbench. –  Michael - sqlbot Aug 16 '13 at 1:06
    
The StartPos and EndPos columns have exchanged data in the table and the query output you show. Is that a mistake or intentional? –  ypercube Aug 17 '13 at 8:09
    
It would also help if you showed the actual data that are joined, for example the 3 rows from the Locus table with AT3G05690, AT3G05690 and AT3G05690 –  ypercube Aug 17 '13 at 8:10
    
ypercube, the exchanged data between the locus table and the query output was definitely a mistake, and the 3 rows with AT3G05690 is just a representation of the case in which there was 3 results from DMRs table that fulfilled the condition where the StartPos and EndPos were contained in the range defined by LOCUS StartPoint and EndPoint. –  LYONG Aug 19 '13 at 17:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.