Inner Join on multiple tables

[omegatemplar]

I have 20 or so tables which share one field.

To start off on a low level I want to join all the fields of the tables on the common field, but I am having trouble getting rid of the common field being duplicated in the results

SELECT * FROM table1 INNER JOIN table2 ON table1.a = table2.a

Do I explicitly state all the fields I want in the result or is there a way to have the common field show up a single time instead of multiple times?

 

[PHV]

Do I explicitly state all the fields I want in the result
Yes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

i think if you use SELECT * FROM X NATURAL INNER JOIN Y then it won't give you two of every pair of columns that have the same name (i.e. the columns it joins on)

but i don't know, and i don't feel like testing it (and i wouldn't be surprised if it varied from one database to another), because i am fundamentally against NATURAL joins on principle



r937.com | rudy.ca

 

[juliane26]

go with PH:
explicitly state all the fields you want in the result

and if you use a where clause instead of inner join depending on your DBMS you are sometimes better off giving the optimizer more freedom in choosing join strategies.


Juliane