Stitch two mysql tables with a common column

Question

I have two tables like this,

Table1 :

+-------------+--------+--------+--------+
| contract_id | price1 | price2 | price3 |
+-------------+--------+--------+--------+
|           1 |     23 |     45 |     56 |
|           1 |     22 |     21 |    453 |
|           1 |     45 |    564 |    456 |
|           4 |     22 |     21 |    453 |
|           5 |     45 |    564 |    456 |
+-------------+--------+--------+--------+

and Table2,

+-------------+--------+---------+
| contract_id | owner  | address |
+-------------+--------+---------+
|           1 | Me     | Madras  |
|           1 | father | Chennai |
+-------------+--------+---------+

I want to stitch both the tables together so the result appears like,

+-------------+--------+--------+--------+--------+---------+
| contract_id | price1 | price2 | price3 | owner  | address |
+-------------+--------+--------+--------+--------+---------+
|           1 |     23 |     45 |     56 | Me     | Madras  |
|           1 |     22 |     21 |    453 | father | Chennai |
|           1 |     45 |    564 |    456 | NULL   | NULL    |
|           4 |     22 |     21 |    453 | NULL   | NULL    |
|           5 |     45 |    564 |    456 | NULL   | NULL    |
+-------------+--------+--------+--------+--------+---------+

Currently I'm manually iterating through the second table and updating the corresponding rows in first table to acheive this. Other approach I figured out is to do a outer join and clean up the duplicate rows. Is there a more better way to acheive the result?

@Thilo They are not duplicate rows per se. But when I do a join. The first 3 records on the left side match with 2 records on right side, as I'm doing a join with contract_id, so that returns 6 rows totally. And the result I want is a bit different. — tamizhgeek, Aug 17 '12 at 09:28
Why one row with `contract_id=1` should be related to `Me-Madras`, another to `father-Chennai` and another to none at all? — ypercubeᵀᴹ, Aug 17 '12 at 09:38
@ypercube Because as I said it is not a join. It is stitching two tables together with a common column. And that common column is contract_id — tamizhgeek, Aug 17 '12 at 10:59
This kind of "stitching" is nothing more than a join. There should be another column to help distinguish which combinations are valid and which are not. Otherwise the result is not determinate, it depends on the order you get your rows from the two tables - so to "stitch" them. — ypercubeᵀᴹ, Aug 17 '12 at 11:01
@ypercube Can you atleast understand the result I want? That is definitely not a join. — tamizhgeek, Aug 21 '12 at 10:53
@ypercube I cant explain more than this. Ok will try again. I have three rows in table1 with contract_id = 1. I put down all the three rows. Then I come to second table and I have two rows with contract_id = 1 , I put down those rows also sequentially. When I come to the third row, I have nothing to enter from second table. So I enter null. If you can't understand people better don't blame others for that. I have *clearly* told n times it is not a join. And again and again you are talking about matching?!! I'm done and if you don't understand, please don't bother. Thanks. — tamizhgeek, Aug 21 '12 at 11:22
Define "sequentially" then. What is the order? Tables do not have any implicit order. There is no "first" or "second" or "third" row in tables. — ypercubeᵀᴹ, Aug 21 '12 at 11:28
@ypercube The order in which they are retrieved. I really dont care about the order, until only rows having same contract_id are stitched together. — tamizhgeek, Aug 21 '12 at 11:33

score 2 · Answer 1 · edited Feb 14 '16 at 21:48

2

This should work perfectly. First I am calculating the rank for both the tables and then joining these two tables using LEFT JOIN based on rank so that there won't be any duplicates in output as it's one to one mapping.

Try this query:

SELECT 
    a.contract_id, a.price1, a.price2, a.price3, b.owner, b.address
FROM 
    (SELECT 
         contract_id, price1, price2, price3, (@rank := @rank + 1) AS rank
     FROM 
         table1, (SELECT @rank := 0) tmp) a
LEFT JOIN 
    (SELECT 
         contract_id, owner, address, (@rank := @rank + 1) AS rank
     FROM 
         table2, (SELECT @rank := 0) tmp) b ON a.rank = b.rank
ORDER BY 
    a.rank ASC;

EDITED MORE ACCURATE QUERY: you need to create nested rank for matching contract_ids:

SELECT 
    a.c_id, a.price1, a.price2, a.price3, b.owner, b.address
FROM 
    (SELECT 
         @var_rank := IF(contract_id <> @var_id_prev,1, @var_rank+1) AS vrank,
         @var_id_prev := contract_id AS c_id,
         price1, price2, price3, @rank := (@rank + 1) AS rnk
     FROM 
         table1, (SELECT @var_id_prev := 0) tmp
     ORDER BY 
         contract_id) a
LEFT JOIN 
    (SELECT 
         @var_rank := IF(contract_id <> @var_id_prev,  1, 
         @var_rank+1) AS vrank,
         @var_id_prev := contract_id AS c_id,
         owner, address, @rank := @rank + 1 AS rnk
     FROM 
         table2, (SELECT @var_id_prev := 0) tmp
     ORDER BY 
         contract_id) b ON a.c_id = b.c_id AND a.vrank = b.vrank
ORDER BY 
    a.c_id, a.vrank ASC;

SQL FIDDLE DEMO HERE

But better approach would be to put auto id on both the tables and use that in join condition.

By doing this tables are normalized and it will save additional overhead of creating temporary tables inside memory while calculating the rank. It will also speedup query by using index on these joined columns.

edited Feb 14 '16 at 21:48

marc_s

732,580
175
1,330
1,459

answered Aug 17 '12 at 09:35

Omesh

27,801
6
42
51

1

Do you want to explain how the `auto id` would help? – Jonathan Leffler Aug 17 '12 at 09:58
ya sure. see last part of my post. – Omesh Aug 17 '12 at 10:04
This works to a extent. But it is discounting the contract_id column. The stitching should happen between rows having same contract_id only. – tamizhgeek Aug 17 '12 at 11:00
yes right. It's M:N relation between those two tables so it's better approach to normalize database by adding `PRIMARY KEY` on those two tables and create a third mapping table having foreign key to both the tables. – Omesh Aug 17 '12 at 11:08
@Omesh Thanks for the help. The query you gave is throwing some error and I can't make much out of it. I have created a fiddle out of it http://sqlfiddle.com/#!2/fd4a6/7 Can you please check the error? – tamizhgeek Aug 21 '12 at 12:20
I have not tested this query so there might be some little mistake. See sql-fiddle given by @ypercube. Good work ypercube! – Omesh Aug 21 '12 at 16:59
@Omesh: Feel free to edit your answer and include that SQL-Fiddle (and/or improve its layout) – ypercubeᵀᴹ Aug 23 '12 at 11:48
thanks! It's working great! Updated SQLFIDDLE in my post: http://sqlfiddle.com/#!2/fd4a6/41 – Omesh Aug 23 '12 at 12:19

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

1

We have to join both table by it's Row_Number.

Try this:

SELECT a.contract_id, a.price1, a.price2, a.price3, b.owner, b.address
FROM (SELECT contract_id, price1, price2, price3, (@rank := @rank + 1) AS rank
      FROM t1, (SELECT @rank := 0) tmp
     )a
LEFT JOIN 
     (SELECT contract_id, owner, address, (@rank1 := @rank1 + 1) AS rank
      FROM t2, (SELECT @rank1 := 0) tmp
     )b
ON a.rank = b.rank
AND a.contract_id = b.contract_id
ORDER BY a.rank ASC;

See this SQLFiddle

edited May 23 '17 at 12:31

Community

1
1

answered Aug 17 '12 at 09:17

Himanshu

31,810
31
111
133

Is that UNION simulating an OUTER JOIN ? – Thilo Aug 17 '12 at 09:18
This is what I'm doing right now. But this is not a complete answer. I have to simulate this outer join and cleanup the result set to get what I want. – tamizhgeek Aug 17 '12 at 09:22
@hims056 Thanks! But please see my comment on Omer's answer and this fiddle - http://sqlfiddle.com/#!2/2fb84/1 The rows from right table are stitched to wrong contract_id rows from left table. – tamizhgeek Aug 17 '12 at 11:03
@tamizhgeek did you see the fiddle given in my answer? – Himanshu Aug 17 '12 at 11:06
@hims056 yes I made my fiddle after altering yours only. When I added three more rows to second table, the result becomes messy. – tamizhgeek Aug 17 '12 at 11:08
@tamizhgeek I added three more rows to second table [see this fiddel](http://sqlfiddle.com/#!2/30c07/1) – Himanshu Aug 17 '12 at 11:12
@hims056 As you can see in your fiddle, 'father-chennai' and 'bro-mumbai' have contract_id as 2 and 3, but are stitched to rows with contract_id 1. That is what I meant. – tamizhgeek Aug 17 '12 at 11:15
@tamizhgeek Updated the answer and fiddle. – Himanshu Aug 17 '12 at 11:20
@ypercube I never said it is what exactly I need. But it gave a lot of insight for me to attack the problem. And you didn't give any useful suggestion, but come eagerly to nitpick others. Kthxbai. – tamizhgeek Aug 21 '12 at 10:52

mintobit · Answer 3 · 2012-08-17T10:14:26.240

0

Instead of comlicating the query, you need to show the relation between these two tables. Normalize the first table and use foreign key to cross-reference related data. I would do it like this:

enter image description here

The query would look like:

SELECT table1.contract_id, table1.price1, table1.price2, table1.price3, table2.name, table2.address
FROM table1
LEFT JOIN table2 ON table1.owner_id = table2.id;

edited Aug 17 '12 at 10:14

answered Aug 17 '12 at 09:56

mintobit

2,363
2
15
15

the t1 and t2 are basically derived tables from subqueries. – tamizhgeek Aug 17 '12 at 11:04
1

@tamizhgeek then you should provide the original tables structure and query you are using in order SO community to be able to help you. – mintobit Aug 17 '12 at 11:07

Stitch two mysql tables with a common column

3 Answers3

See this SQLFiddle