MySQL: JOINs on 1-to-1 basis

Question

I think, this problem is of more advanced SQL category (MySQL in this case): I have two tables (TABLE_FRUIT, TABLE_ORIGIN - just example names) which have columns that can be joined (fruit_name).

Consider the following diagram:

TABLE_FRUIT
fruit_id|fruit_name  |variety
--------|----------------------
       1|Orange      |sweet
       2|Orange      |large
       3|Lemon       |wild
       4|Apple       |red
       5|Apple       |yellow
       6|Pear        |early
etc...

TABLE_ORIGIN
fuit_id  |fruit_name|Origin
---------|----------|--------
        1|Apple     | Italy
        2|Pear      | Portugal
        3|Grape     | Italy
        4|Orange    | Spain
        5|Orange    | Portugal
        6|Orange    | Italy
etc...      



Desired Result:     
TABLE_FRUIT_ORIGIN
fuit_id  |fruit_name|Origin
---------|----------|--------
        1|Orange    | Spain
        2|Orange    | Portugal
        3|Apple     | Italy
        4|Pear      | Portugal

The tables have multiple identical values in columns that compose the joins(fruit_name). Despite that, I need to join the values on 1-to-1 basis. In other words, there is "Orange" value 2 times in TABLE_FRUIT and 3 times in TABLE_ORIGIN. I am looking for a result of two matches, one for Spain, one for Portugal. Italy value from TABLE_ORIGIN must be ignored, because there is no available third Orange value in TABLE_FRUIT to match Orange value in TABLE_ORIGIN.

I tried what I could, but I can not find anything relevant on Google. For example, I tried adding one more column record_used and tried UPDATE but without success.

TABLE_ORIGIN
    fuit_id  |fruit_name|origin     |record_used
    ---------|----------|-----------|-----------
            1|Apple     | Italy     |
            2|Pear      | Portugal  |
            3|Grape     | Italy     |
            4|Orange    | Spain     |
            5|Orange    | Portugal  |
            6|Orange    | Italy     |
    etc...      




UPDATE
    TABLE_FRUIT t1
INNER JOIN
    TABLE_ORIGIN t2
ON
    (t1.fruit_name = t2.fruit_name)
AND
    (t2.record_used IS NULL)
SET
    t2.record_used = 1;

Summary:

• Find matching records between two tables on 1-to-1 basis (probably JOIN)
• For each record in TABLE_FRUIT find just one (next first) matching record in TABLE_ORIGIN
• If a record in TABLE_ORIGIN was already matched once with a record from TABLE_FRUIT, it may not be considered again in the same query run.

Answer 1

Here is what I had in mind with RANK function. After commenting, I realized mysql doesn't have a built in RANK over GROUP BY function so had to find this work around.

SELECT * 
FROM   (SELECT fruit_name, 
               @f_rank := IF(@f_name = fruit_name, @f_rank + 1, 1) AS rank, 
               @f_name := fruit_name 
        FROM   table_fruit 
        ORDER  BY fruit_name DESC) f 
       INNER JOIN (SELECT fruit_name, 
                          @f_rank := IF(@f_name = fruit_name, @f_rank + 1, 1) AS 
                          rank, 
                          @f_name := fruit_name 
                   FROM   table_origin 
                   ORDER  BY fruit_name DESC) o 
               ON f.fruit_name = o.fruit_name 
                  AND f.rank = o.rank;

Explanation: Rank each item in the table for each fruit. So Orange in the first table would have rank 1 and 2 and so will Apple. In the second table, Orange will have rank 1, 2 and 3 but others will only have rank 1. Then when joining the tables based on names, you can also join based on rank so that way, you'll get Orange rank 1 and 2 match but Orange with rank 3 will not match.

This is based on my understanding of the problem. Let me know if the requirement is something different than what I have given here.