Mysql inner join on two subqueries returning duplicate rows

Question

We have a table like,

Table1(Contract_id, name, address, contact_no)

and another table like,

Table2(Contract_id, approver, owner, authority)

Sample data:

mysql> select * from Table1;
+-------------+-------+---------+------------+
| contract_id | owner | address | contact_no |
+-------------+-------+---------+------------+
|       11111 | XXX   | Madurai | 897161     |
|       12456 | XYZ   | Madras  | 897161     |
|       11111 | XYZ   | Madras  | 897161     |
+-------------+-------+---------+------------+
3 rows in set (0.00 sec)

mysql> select * from Table2;
+-------------+----------+
| contract_id | approver |
+-------------+----------+
|       11111 | YZX      |
|       11112 | YYY      |
+-------------+----------+
2 rows in set (0.00 sec)

I have written a query to get all contract_ids and matching data for a criteria like this,

"Get all contracts with Owner like 'X' and address like "Mad" and approver = 'YZX'"

select contract_id,owner,address,approver 
from 
(
    select *
    from Table1 
    where owner like '%X%' 
    and address like '%Mad%'
) t1 
inner join 
(
    select * 
    from Table2 
    where approver = 'YZX'
) t2 using (contract_id);

It is returning the results correctly. But the problem is left table has two matching rows and right table has only one matching row. So the row in right table is duplicated twice.

> +-------------+---------------+-----------+-------------+
> |contract_id  |  owner        | address   | approver    |
> +-------------+---------------+-----------+-------------+
> |11111        | XXX           | Madurai   | YZX         |
> +-------------+---------------+-----------+-------------+
> |11111        | XYZ           | Madras    | YZX         | 
> +-------------+---------------+-----------+-------------+

The approver value is duplicated twice. Can i somehow avoid this mysql itself? I want null value for the second row's approver column.

EDIT 1:

I edited my query to have group by approver at the end,

select contract_id,owner,address,approver 
from 
(
    select contract_id
    from Table1 
    where owner like '%X%' 
    and address like '%Mad%'
) t1 
inner join 
(
    select contract_id 
    from Table2 
    where approver = 'YZX'
) t2 using (contract_id) group by approver;

Now the results became like,

> +-------------+---------------+-----------+-------------+
> |contract_id  |  owner        | address   | approver    |
> +-------------+---------------+-----------+-------------+
> |11111        | XYZ           | Madurai   | YZX         |
> +-------------+---------------+-----------+-------------+

The second went missing now. I want that as well.

EDIT 2: Added the sample data and exact queries.

EDIT 3: I want the results to be formatted like the below,

> +-------------+---------------+-----------+-------------+
> |contract_id  |  owner        | address   | approver    |
> +-------------+---------------+-----------+-------------+
> |11111        | XXX           | Madurai   | YZX         |
> +-------------+---------------+-----------+-------------+
> |11111        | XYZ           | Madras    | NULL        | 
> +-------------+---------------+-----------+-------------+

EDIT 4: I acheived what I wanted. Below is the query I used.

create temporary table table1 select * 
from 
(
    select *
    from Table1 
    where owner like '%X%' 
    and address like '%Mad%'
) t1 
inner join 
(
    select * 
    from Table2 
    where approver = 'YZX'
) t2 using (contract_id) limit 1;

and the query,

insert into table1 select contract_id, IF((select count(*) from table1 where owner = t.owner and contract_id = t.contract_id) > 0, NULL, t.contract_id),IF((select count(*) from table1 where address = t.adress and contract_id = t.contract_id) > 0, NULL, t.contract_id),IF((select count(*) from table1 where approver = t.approver and contract_id = t.contract_id) > 0, NULL, t.contract_id)
from 
((
    select *
    from Table1 
    where owner like '%X%' 
    and address like '%Mad%'
) t1 
inner join 
(
    select * 
    from Table2 
    where approver = 'YZX'
) t2 using (contract_id)) t;

can you post some sample data for both tables?

Taryn
– Taryn

2012-08-10 11:48:23 +00:00
Commented Aug 10, 2012 at 11:48 — Taryn
– Taryn, Commented Aug 10, 2012 at 11:48

Taryn · Accepted Answer · 2012-08-10 13:08:27Z

3

Just to be clear the data is not duplicating. Since you are joining the two tables on contract_id and the same contract id is associated with two records, you will have the approver appear twice.

mysql> select * from Table1;
+-------------+-------+---------+------------+
| contract_id | owner | address | contact_no |
+-------------+-------+---------+------------+
|       11111 | XXX   | Madurai | 897161     | -- contract_id 11111
|       12456 | XYZ   | Madras  | 897161     |
|       11111 | XYZ   | Madras  | 897161     | -- contract_id 11111
+-------------+-------+---------+------------+
3 rows in set (0.00 sec)

mysql> select * from Table2;
+-------------+----------+
| contract_id | approver |
+-------------+----------+
|       11111 | YZX      | -- contract_id 11111
|       11112 | YYY      |
+-------------+----------+
2 rows in set (0.00 sec)

So the result will always be two records from table1 and one record from table two with an approver = 'YZX'

On another note, I might consider rewriting the query to the following:

select t1.contract_id, t1.owner, t1.address, t2.approver 
from table1 t1
inner join table2 t2
  on t1.contract_id = t2.contract_id
where t1.owner like '%X%'
  and t1.address like '%Mad%'
  and t2.approver = 'YZX'

Based on your comment, I am beginning to think you might want a LEFT JOIN:

select t1.contract_id, t1.owner, t1.address, t2.approver 
from table1 t1
left join table2 t2
  on t1.contract_id = t2.contract_id
  and t2.approver = 'YZX'
where t1.owner like '%X%'
  and t1.address like '%Mad%'

See SQL Fiddle with Demo

Edit 2: There is not simple way to force this to use a null for additional fields. You can attempt to use a rownumber to get the results:

select x.contract_id, 
  x.owner,
  x.address,
  case when (@rownum:=@rownum+1 = 1) then x.approver else null end approver
from 
(
  select t1.contract_id, t1.owner, t1.address, t2.approver
  from table1 t1
  inner join table2 t2
    on t1.contract_id = t2.contract_id
    and t2.approver = 'YZX'
  where t1.owner like '%X%'
    and t1.address like '%Mad%'
) x, (SELECT @rownum:=0) r

See SQL Fiddle with Demo

edited Aug 10, 2012 at 13:08

answered Aug 10, 2012 at 12:12

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tamizhgeek Over a year ago

Where the contract_id is same? On table2? Two rows are having different ids only no?

Taryn Over a year ago

Table2 has a contract_id equal to the result in Table1.

Taryn Over a year ago

@tamizhgeek see my edit, I think you might want a LEFT JOIN

tamizhgeek Over a year ago

Sorry for the wrong wording :( I know this is how join works. I didnt mean to say mysql is duplicating rows. All I wanted to ask is can I execute some query so that the matched row from Table2 appears only once and returns NULL for all other rows?

tamizhgeek Over a year ago

No I dont want a left join. I want rows only when there is a match on both sides. But only that the matching row should appear only once.

|

Omesh · Accepted Answer · 2012-08-10 11:45:50Z

1

Use DISTINCT clause to avoid duplicates:

SELECT contract_id,owner,address,approver
from (select DISTINCT contract_id
      from Table1 where owner like '%X%' and address like '%Mad%'
     ) t1
     inner join (select contract_id from Table2 where approver = 'YZX') t2
        using (contract_id);

answered Aug 10, 2012 at 11:45

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

1 Comment

tamizhgeek Over a year ago

No luck! DISTINCT still gives the same results.

symcbean · Accepted Answer · 2012-08-10 12:11:14Z

1

It is not duplicated, It's showing exactly what it should show.

If you explained your criteria for which record you want to show and which record you want to exclude from the result set then maybe we could have a go at writing something which meets your requirement.

The query you have provided in your first update maybe syntactically valid on MySQL (it won't work on other DBs) but is semantic gibberish - how did you tell the DBMS that you really wanted to exclude the case where owner=XXX? Or address=Madras?

answered Aug 10, 2012 at 12:11

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

2 Comments

tamizhgeek Over a year ago

My query is simple. may be I have over complicated it. blame my bad english and my company for not allowing to show the real data. I have a common column in two tables. I want to join those tables with that column and select some criteria. I accept it is returning the data right only. But some how can I make the second row's right table values appear as NULL?

symcbean Over a year ago

Then simply don't include those columns (owner, address) in the output and use a group by or difstinct modifier.

Collectives™ on Stack Overflow

Mysql inner join on two subqueries returning duplicate rows

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related