Using JOINS in MySQL, an additional table is created?

Question

SELECT d.user_guid AS dUserID, c.user_guid AS cUserID, d.dog_guid AS dDogID,
c.dog_guid AS dDogID, count(test_name)
FROM dogs d LEFT JOIN complete_tests c
ON d.dog_guid=c.dog_guid
GROUP BY d.dog_guid;

When writing this code, a table was created that was labeled..."dDOGId_1"

the output is virtually the same with the exception of some that have the word "non" in the rows

Is this where the JOIN is happening? Also where is this coming from? Is there something wrong in my code that is creating this additional column?

Answer 1

What? You seem very confused. This is your select:

SELECT d.user_guid AS dUserID, c.user_guid AS cUserID, d.dog_guid AS dDogID,
       c.dog_guid AS dDogID, count(test_name)

No addition table is created. Your result has two columns called dDogID. To distinguish between them, the application is adding a _1.

However, including both is entirely unnecessary, because the left join implies they are the same. And the count() should be counting matches. I assume this is the query you intend:

SELECT d.user_guid AS dUserID, d.dog_guid AS dDogID,
       count(c.test_name)
FROM dogs d LEFT JOIN
     complete_tests c
     ON d.dog_guid=c.dog_guid
GROUP BY d.dog_guid;

The c.user_guid doesn't really make sense in the SELECT, because it is unclear what row it should be coming from. Perhaps you want MIN(c.user_guid), a MAX(), or GROUP_CONCAT().

Answer 2

This is happening because you have 2 columns labeled with the same name. So the second one is defaulted to _1

It has nothing to do with the join