Avoid a Cartesian plan in a Join with 2 tables with duplicities

Asked

Viewed 920 times

0

TABLEA E TABLEB

According to the image above:

The field A1 of TableA has two duplicate values which is key 3 and the TableB with the countryside B1 has the same duplicate values.

I wanted to make a inner join between the two tables using the fields A1 of TableA and B1 of TableB as keys and bring as results the field values A2 and B2 without generating a Cartesian.

Intended result:

A2 B2 
a   a
b   a    
c   c 
d   f 

I tried to make:

Select  A2, b.B2 
    from Table_A as a 
    inner join Table_B  as b
    on a.a1 = b.b1  

Result acquired with the query above:

A2, B2
a   a
b   a
c   c
d   c
c   f
d   f
  • Deco, I saw that you have not yet done the [tour] by the site, so I invite you to do it. It will be important for you to learn the basics of how the site works.

  • Is there any way you can better detail what kind of relationship this is? And let us know which database you are using. You can add a link so to exemplify what you’re trying to do.

  • Does this information represent something real? Because you seem to have implemented a many-to-many relationship, but you don’t want that. What is the relationship between c and d in TableA to have the same value as A1? And why this value varied to d? The same for the table Table2. The structure of the bank is very confused and does not seem to be ideal to represent the relationship between the records.

  • This is just the intended result, not the description of the relationship between the tables. Do you know about relational databases? What are these records? Why do these values repeat? Why don’t you want the product between the tables? It doesn’t make sense c and f have the same value is not related. The same with d and c, so I say that it seems that the structure of the tables does not match the desired relation. Please explain, not only with the expected result.

  • This isn’t something real, it’s just a test I got. And I would like to know if within this chaotic scenario using an Inner Join it would be possible not to get a Cartesian, ie in the field: A1 the first value 3 if it corresponds with the first value 3 of the field:B1 ,e that the second value 3 of the field: A1 if it corresponds to the second value 3 of field:B1. Thus avoiding a Cartesian. Of course, the other keys would correspond normally because there are no duplicates in keys 1 and 2 of both tables. I don’t know if I could express myself well. I thank you. The tour was done. Gracias

  • I know that using Distinct this does not work, because the line is not duplicated but the field used as key. If within this scenario has nothing to do other than accept the Cartesian td well. I believe this is a prank from the company that applied the test and so I would like to know if there are other possibilities to solve this? Maybe create new indices for each table?

  • Thank you so much for your help Anderson Carlos Woss, people just like you need. Sowing knowledge.

  • I don’t know if this was ironic, but I hope it’s not. If it is, review the community concepts by redoing the tour. Since no one has answered yet, it may be impossible to do as it is. But perhaps the question just hasn’t had enough attention. If you want, you can edit it briefly and it will go back to the home page. When you have a reputation, you can offer a reward for the answer. Good luck.

Show 3 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.