I'm doing a select in MySQL with a three-table join (shown in the lower right quadrant of the below image).  Relevant data in the tables are shown along the left side.  The result is in the upper right quadrant.  (Focus on the things in the red boxes.)

enter image description here

My ideal result is two rows of data, but I'm getting four.  I don't know why there are four rows of data.

New contributor
user1036877 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 4
    These "duplicates" are NOT duplicates! Because some field values differs. If you want "one record in output for each record in first table" then you must use a subquery instead second table which groups the data and selects one record per group by referenced fieldset. – Akina May 16 at 4:35

To expand on what @Akina seems to be saying:

Your situation seems to be something like this:

Table A

id | (other fields)
65 |     (data)(more data)
Table B
id | color | (other fields)
65 | red   |     (data)
65 | blue  |        ︙
 ︙   (more data)
Table C
id | animal | (other fields)
65 | cat    |     (data)
65 | dog    |        ︙
 ︙     (more data)
and you’re joining the three tables on the id field. Assuming that there are no other id matches than 65, you can expect to get
id | color | animal | (other fields)
65 | red   | cat    |     (data)
65 | red   | dog    |       ︙
65 | blue  | cat    |       ︙
65 | blue  | dog    |       ︙
In general, if you have N matching rows in Table B and M matching rows in Table C, you can expect to have N × M rows in the join.

Your Answer

user1036877 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.