MySQL not showing duplicate column names in separate tables using INNER JOIN

Question

I have a database with 22 tables most tables have a few column fields with identical names. When I use INNER JOIN on the tables with the duplicate column names it will ignore them in the last table. Right now I am only joining 3 tables

SELECT * FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

So in this example typeofdealer and typeofservices both contain column names Other and OtherText When I get this query back I only see one Other and one OtherText.

I just discovered as I was writing this that if I do the query in "phpmyadmin" that I get it back exactly as intended. I am doing this query using prepared statements in php and outputting the results using:

echo "<PRE>";
print_r($results);
echo "</PRE>";

I believe the problem is that php arrays cannot contain duplicate fields so is there a way to circumvent the issue I am having?

Did you check that the result does not have additional columns like Other_1, usually if the result has identical column names, it would try to use aliases — Jag
– Jag, Commented Feb 17, 2014 at 17:53
using var_dump does not list any aliases it basically removes the matching field names — Yamaha32088
– Yamaha32088, Commented Feb 17, 2014 at 17:56

Justin Paul Paño · Accepted Answer · 2014-02-17 17:56:27Z

1

The workaround you can do for this is to use an alias AS to make a distinction between columns with the same names. Something like:

SELECT PI.Other AS Other1, PI.OtherText AS OtherText2, TS.Other AS Other2, TS.OtherText AS OtherText2 FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

Actually it will work the same way if you omit the AS keyword say SELECT PI.Other Other1, PI.OtherText OtherText2

answered Feb 17, 2014 at 17:56

Justin Paul Paño

9365 silver badges7 bronze badges

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

Comments

ollieread · Accepted Answer · 2014-02-17 17:56:08Z

1

The problem is, as you rightfully suggested, that PHP won't allow multiple entries into an array with the same key name.

The simplest way around this, is to alias the fields in the column selection section of the query, like so:

SELECT *, typeofservices.Other as ServicesOther, typeofservices.OtherText as ServicesOtherText FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

It's not pretty but it's simple.

answered Feb 17, 2014 at 17:56

ollieread

6,3211 gold badge22 silver badges36 bronze badges

1 Comment

Yamaha32088 Over a year ago

Do you know of an SQL query to find duplicate column names?

Collectives™ on Stack Overflow

MySQL not showing duplicate column names in separate tables using INNER JOIN

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related