inner join of one table and prevent duplicate?

I have a table called

trade with the following column (removed alot of redundant data)

trade_id,buyer_company_code, seller_company_code, legacy_trade_number.

I intent to query all trade where there one trade's buyer_company_code = another trade's seller_company_code and the trade's seller_company_code = another buyer buyer_company code

I tried with the query that result in the screenshot below

select * from trade tradetable1 inner join trade tradetable2

 on tradetable1.seller_company_code=tradetable2.buyer_company_code and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

enter image description here

It currently gives me the following (legacy_trade_number from one trade) and (legacy_trade_numbe from another trade) with duplicates (due to cartesian product)

5548 5554

5548 5555

5548 5556

5549 5554

5549 5555

5549 5556

5550 5554

5550 5555

5550 5556

but i requires it to be as follows

5548 5554

5549 5555

5550 5556

Where the two column have no duplicate value. Any suggestion/hint will be helpful! thanks

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

Your current sample result shows 5554, 5555, and 5556 in the second column next to 5548 in the first. It looks like you only want to see the lowest of those three second-column values. Is that correct? Please edit your question.

– O. Jones
Nov 14 '18 at 17:46

i wanted the two column combination to be unique with only one appearing on each side, without duplicate. Was wondering if this could be sql doable. I know i can easily use my query result i got above and filter them in my application code

– ericlee
Nov 14 '18 at 17:50

1

It is doable of course. But you have not yet specified the problem well enough to solve in SQL. The problem is, several trade numbers in tradetable1 match the criterion tradetable1.legacy_trade_number<tradetable2.legacy_trade_number in your ON condition. Which of those several do you want to display? The least one? When working with SQL, you are describing sets of values.

– O. Jones
Nov 14 '18 at 17:54

add a comment |

I have a table called

trade with the following column (removed alot of redundant data)

trade_id,buyer_company_code, seller_company_code, legacy_trade_number.

I intent to query all trade where there one trade's buyer_company_code = another trade's seller_company_code and the trade's seller_company_code = another buyer buyer_company code

I tried with the query that result in the screenshot below

select * from trade tradetable1 inner join trade tradetable2

 on tradetable1.seller_company_code=tradetable2.buyer_company_code and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

enter image description here

It currently gives me the following (legacy_trade_number from one trade) and (legacy_trade_numbe from another trade) with duplicates (due to cartesian product)

5548 5554

5548 5555

5548 5556

5549 5554

5549 5555

5549 5556

5550 5554

5550 5555

5550 5556

but i requires it to be as follows

5548 5554

5549 5555

5550 5556

Where the two column have no duplicate value. Any suggestion/hint will be helpful! thanks

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

Your current sample result shows 5554, 5555, and 5556 in the second column next to 5548 in the first. It looks like you only want to see the lowest of those three second-column values. Is that correct? Please edit your question.

– O. Jones
Nov 14 '18 at 17:46

i wanted the two column combination to be unique with only one appearing on each side, without duplicate. Was wondering if this could be sql doable. I know i can easily use my query result i got above and filter them in my application code

– ericlee
Nov 14 '18 at 17:50

1

It is doable of course. But you have not yet specified the problem well enough to solve in SQL. The problem is, several trade numbers in tradetable1 match the criterion tradetable1.legacy_trade_number<tradetable2.legacy_trade_number in your ON condition. Which of those several do you want to display? The least one? When working with SQL, you are describing sets of values.

– O. Jones
Nov 14 '18 at 17:54

add a comment |

I have a table called

trade with the following column (removed alot of redundant data)

trade_id,buyer_company_code, seller_company_code, legacy_trade_number.

I intent to query all trade where there one trade's buyer_company_code = another trade's seller_company_code and the trade's seller_company_code = another buyer buyer_company code

I tried with the query that result in the screenshot below

select * from trade tradetable1 inner join trade tradetable2

 on tradetable1.seller_company_code=tradetable2.buyer_company_code and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

enter image description here

It currently gives me the following (legacy_trade_number from one trade) and (legacy_trade_numbe from another trade) with duplicates (due to cartesian product)

5548 5554

5548 5555

5548 5556

5549 5554

5549 5555

5549 5556

5550 5554

5550 5555

5550 5556

but i requires it to be as follows

5548 5554

5549 5555

5550 5556

Where the two column have no duplicate value. Any suggestion/hint will be helpful! thanks

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

I have a table called

trade with the following column (removed alot of redundant data)

trade_id,buyer_company_code, seller_company_code, legacy_trade_number.

I intent to query all trade where there one trade's buyer_company_code = another trade's seller_company_code and the trade's seller_company_code = another buyer buyer_company code

I tried with the query that result in the screenshot below

select * from trade tradetable1 inner join trade tradetable2

 on tradetable1.seller_company_code=tradetable2.buyer_company_code and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

enter image description here

It currently gives me the following (legacy_trade_number from one trade) and (legacy_trade_numbe from another trade) with duplicates (due to cartesian product)

5548 5554

5548 5555

5548 5556

5549 5554

5549 5555

5549 5556

5550 5554

5550 5555

5550 5556

but i requires it to be as follows

5548 5554

5549 5555

5550 5556

Where the two column have no duplicate value. Any suggestion/hint will be helpful! thanks

mysql sql

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

edited Nov 14 '18 at 17:46

asked Nov 14 '18 at 17:25

ericlee

1,20163261

asked Nov 14 '18 at 17:25

ericlee

1,20163261

asked Nov 14 '18 at 17:25

ericlee

1,20163261

Your current sample result shows 5554, 5555, and 5556 in the second column next to 5548 in the first. It looks like you only want to see the lowest of those three second-column values. Is that correct? Please edit your question.

– O. Jones
Nov 14 '18 at 17:46

i wanted the two column combination to be unique with only one appearing on each side, without duplicate. Was wondering if this could be sql doable. I know i can easily use my query result i got above and filter them in my application code

– ericlee
Nov 14 '18 at 17:50

1

It is doable of course. But you have not yet specified the problem well enough to solve in SQL. The problem is, several trade numbers in tradetable1 match the criterion tradetable1.legacy_trade_number<tradetable2.legacy_trade_number in your ON condition. Which of those several do you want to display? The least one? When working with SQL, you are describing sets of values.

– O. Jones
Nov 14 '18 at 17:54

add a comment |

Your current sample result shows 5554, 5555, and 5556 in the second column next to 5548 in the first. It looks like you only want to see the lowest of those three second-column values. Is that correct? Please edit your question.

– O. Jones
Nov 14 '18 at 17:46

i wanted the two column combination to be unique with only one appearing on each side, without duplicate. Was wondering if this could be sql doable. I know i can easily use my query result i got above and filter them in my application code

– ericlee
Nov 14 '18 at 17:50

1

It is doable of course. But you have not yet specified the problem well enough to solve in SQL. The problem is, several trade numbers in tradetable1 match the criterion tradetable1.legacy_trade_number<tradetable2.legacy_trade_number in your ON condition. Which of those several do you want to display? The least one? When working with SQL, you are describing sets of values.

– O. Jones
Nov 14 '18 at 17:54

Your current sample result shows 5554, 5555, and 5556 in the second column next to 5548 in the first. It looks like you only want to see the lowest of those three second-column values. Is that correct? Please edit your question.

– O. Jones
Nov 14 '18 at 17:46

i wanted the two column combination to be unique with only one appearing on each side, without duplicate. Was wondering if this could be sql doable. I know i can easily use my query result i got above and filter them in my application code

– ericlee
Nov 14 '18 at 17:50

It is doable of course. But you have not yet specified the problem well enough to solve in SQL. The problem is, several trade numbers in tradetable1 match the criterion tradetable1.legacy_trade_number<tradetable2.legacy_trade_number in your ON condition. Which of those several do you want to display? The least one? When working with SQL, you are describing sets of values.

– O. Jones
Nov 14 '18 at 17:54

add a comment |

1 Answer
1

active

oldest

votes

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (

    select * from trade 

) tradetable1 

inner join ( 

select * 

from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 

    and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

  group by t1.legacy_trade_number

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

1

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305689%2finner-join-of-one-table-and-prevent-duplicate%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (

    select * from trade 

) tradetable1 

inner join ( 

select * 

from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 

    and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

  group by t1.legacy_trade_number

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

1

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

add a comment |

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (

    select * from trade 

) tradetable1 

inner join ( 

select * 

from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 

    and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

  group by t1.legacy_trade_number

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

1

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

add a comment |

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (

    select * from trade 

) tradetable1 

inner join ( 

select * 

from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 

    and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

  group by t1.legacy_trade_number

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

if you don't want duplicated result for the column legacy_trade_number then instead of select * you should select only the columns you really need

select distinct tradetable1.legacy_trade_number from (

    select * from trade 

) tradetable1 

inner join ( 

select * 

from trade ) tradetable2 on tradetable1.seller_company_code=tradetable2.buyer_company_code 

    and tradetable1.buyer_company_code=tradetable2.seller_company_code

    and tradetable1.legacy_trade_number<tradetable2.legacy_trade_number

any way the result could be obtained simple using

select disticnt t1.legacy_trade_number 

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

The duplication in evaluated at row level so .. if you need others column you should choose which value you want and use aggreagtion function for reduce the unuseful values

eg: using an aggregation function as min() you could

select t1.legacy_trade_number, min(t1.col1), min(t2.coln)

from  trade  t1 

inner join trade t2 on on t1.seller_company_code=t2.buyer_company_code 

    and t1.buyer_company_code=t2.seller_company_code

    and t1.legacy_trade_number<t2.legacy_trade_number

  group by t1.legacy_trade_number

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

edited Nov 14 '18 at 17:32

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

answered Nov 14 '18 at 17:28

scaisEdge

94.3k104970

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

1

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

add a comment |

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

1

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

thanks ! However, this approach result in only one column being shown 18/N00548 18/N00549 18/N00550

– ericlee
Nov 14 '18 at 17:32

answer updated ..

– scaisEdge
Nov 14 '18 at 17:34

added a sample for aggreagted result too.

– scaisEdge
Nov 14 '18 at 17:35

yes i understood this, but there is nothing to aggregate it against. If i have miss out something, please do advice!. The end goal is to get the final two column in my question above.

– ericlee
Nov 14 '18 at 17:37

the result you want is not based on a relational approach .. which is the logic .. for the result .. ?? .

– scaisEdge
Nov 14 '18 at 17:39

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky