One to one join in MySQL

Question

I need to join two table on one common column, but I want to maintain a one-to-one relation on other two column. For example:

table_1

ID_C   ID_ROW_C   OPT
C      1          10   
C      2          10

table_2

ID_F   ID_ROW_F   OPT
F      3          10   
F      4          10

My query:

select *  
from table_1, table_2  
where table_1.OPT=table_2.OPT

result

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F  
C      1          10    F      3  
C      1          10    F      4  
C      2          10    F      3  
C      2          10    F      4

desired result:

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F
C      1          10    F      4  
C      2          10    F      3

or

ID_C   ID_ROW_C   OPT   ID_F   ID_ROW_F
C      1          10    F      3  
C      2          10    F      4

How can I do?

Besides the `OPT` field, how do you relate the columns from each table with the other? You're getting what's expected (if you only join the `OPT` column, you'll get all combinations with the matching rows)... you need to define some criteria to relate the other rows. — Barranka, Sep 22 '15 at 21:44

score 1 · Answer 1 · edited May 23 '17 at 12:34

1

What you need to do is use JOIN.

SELECT * FROM table_1
JOIN table_2
ON table_1.OPT = table_2.OPT

More info from the MySQL manual: https://dev.mysql.com/doc/refman/5.0/en/join.html

And a relevant Stack Overflow discussion on the different types of JOINs: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 12:34

Community

1
1

answered Sep 22 '15 at 15:56

Jake Bathman

1,268
3
18
25

This is not what I have to do. I want to get only 2 rows from this query, not 4. – Walter Bianchi Sep 22 '15 at 21:46
1

Looks like @barranka's answer should get you closer, but I'd have a look at your table design if this is what you want to do. – Jake Bathman Sep 23 '15 at 12:39

score 0 · Answer 2 · answered Sep 22 '15 at 22:19

Since you're not providing any rule to relate the columns, you're getting exactly what you're supposed to get: All the rows of both tables that fulfill the relation.

However, you can create an "artificial" condition to get what you want... it's not pretty, but it will work:

select t1.id_c, t1.id_row_c
     , t1.opt
     , t2.id_f, t2.id_row_f
from
    (
        select @r_id_1 := (case 
                   when @prev_opt_1 = table_1.opt then @r_id_1 + 1 
                   else 1
               end) as r_id
             , table_1.*
             , @prev_opt_1 := table_1.opt as new_opt_1
        from (select @r_id_1 := 0, @prev_opt_1 := 0) as init_1
           , table_1
        order by table_1.opt, table_1.id_row_c
    ) as t1
    inner join (
        select @r_id_2 := (case
                   when @prev_opt_2 = table_2.opt then @r_id_2 + 1 
                   else 1
               end) as r_id
             , table_2.*
             , @prev_opt_2 := table_2.opt as new_opt_2
        from (select @r_id_2 := 0, @prev_opt_2 := 0) as init_2, table_2
        order by table_2.opt, table_2.id_row_f
    ) as t2 on t1.opt = t2.opt and t1.r_id = t2.r_id

See the result at SQL Fiddle.

The explanation

Let's take the first subquery:

select @r_id_1 := (case 
           when @prev_opt_1 = table_1.opt then @r_id_1 + 1 
           else 1
       end) as r_id
     , table_1.*
     , @prev_opt_1 := table_1.opt as new_opt_1
from (select @r_id_1 := 0, @prev_opt_1 := 0) as init_1
   , table_1
order by table_1.opt, table_1.id_row_c

In the from clause for this query, I'm declaring two user variables and initializing them to zero. The @r_id_1 variable will increase by one if the previous value of @prev_opt_1 is equal to the current value of opt, or reset to 1 if the value is different. The variable @prev_opt_1 will take the value of the opt column after the @r_id_1 variable is set. This means that, for each opt value, the @r_id_1 variable will have an increasing value.

The second subquery does exactly the same for the other table.

Finally, the outer-most query will join both subqueries using opt and the increasing Id.

Take the time to understand what's going on behind scenes (execute each subquery separatedly and see what happens).

As I said, this solution is "artificial"... it's a way to get what you need, but to avoid this ~~dirty and quite complex~~ hard solutions, you need to rethink your tables, and make them more easy to relate with each other.

Hope this helps

One to one join in MySQL

2 Answers2