Selecting Fields With the Same Name

I had two people ask me the same question today so I thought I'd share this. The dilemma occurs when joining two or more tables where a field name is the same in those tables. For example, you have a blog table with an id field and a comments table with an id field. If you just did a SELECT * while joining those two tables, you can't use the table identifier to grab the field name (E.g: $rs['blog.id'] won't work).

If you're using MySQL, the way around this is to specify the field names manually and creating aliases for anything that would be a duplicate.

SELECT blog.id AS blog_id, comment.id AS comment_id, blog.title, blog.description, comment.text FROM [...]

If you're using MS SQL Server user, there's a quick shortcut where you can include all fields using the asterisk (*) and still create aliases for the other fields. Keep in mind, this will return blog_id AND blog.id.

SELECT blog.id AS blog_id, comment.id AS comment_id, * FROM [...]

I'm of the school of "return only what you need from the database" and would avoid using the asterisk selector in any case.

Published July 12, 2006 · Updated September 14, 2006
Categorized as SQL
Short URL: https://snook.ca/s/569

Conversation

16 Comments · RSS feed
Miles Johnson said on July 12, 2006

Could be very useful for some, good job.

Nate K said on July 12, 2006

There are other ways around this as well - but this is a nice and simple way of handling this. I am sure this will help some out there.

Where were these articles several years ago when I was asking these questions? hehehe.

Danilo said on July 12, 2006

Or another twist if you always know the order of the columns within the SQL. Take a look at mysql_fetch_array and ask it to pull back the columns with numerical indices from the query rather than (or in addition to) an associative array.

Andy Kant said on July 12, 2006

You can clean up the SQL a bit by dropping the AS parts (you don't need it anyways) and aliasing the tables.

SELECT b.id blog_id, c.id comment_id, b.title, b.description, c.text FROM blog b INNER JOIN comment c ON b.id = c.blog_id

nate said on July 12, 2006

<shameless_plug>
CakePHP auto-organizes SQL fields by model, i.e.:
Array(
'Blog' => array('id' => 1, 'title' => 'Whatever')
'Comment' => array('id' => 5...
);
</shameless_plug>

Maybe this is because I'm running a WebKit nightly, but I can't see the carat in this text field. Makes editing a wee bit difficult.

nate said on July 12, 2006

Just to clarify, the Cake-related part of the above comment should be wrapped in <shameless_plug />.

I wish more blogs had those nifty live-comment preview thingies....

[ED: Have you tried mine? :)]

Jonathan Snook said on July 12, 2006

Nate: I was half tempted to mention it in the article. :) I decided against it as I really wanted the post itself to be relatively agnostic.

Dan said on July 12, 2006

What do you mean about that being a MS SQL user?

SELECT t1.*, t2.blog_id FROM table1 JOIN table2.....

That works on every database server I have ever tried on... I could have misunderstood you though, hehe...

Jonathan Snook said on July 12, 2006

Dan: take notice that in the example for MS SQL users, I've just done * to include all fields. So, using your example, I couldn't do:

SELECT *, t2.blog_id [...]

This would generate an error message. However, upon further testing, it seems that you can reselect all fields by specifying the table name (or alias) as you've done and this will work in MySQL as well as MS SQL Server.

jlactose said on July 13, 2006

You should take a look at this page in ie, Jon-o *dodges holy water*, your code boxes are a bit tight!

Jason Kataropoulos said on July 13, 2006

I tend to give aliases. I feel that I have tha control so I feel safer.

Karinne said on July 13, 2006

Hmm ... I just name my ID's according to the table when I create them. For example, if it's a blog table, the id would be bid, if it's a comments table, it'll be cid and so on. Unless I totally misunderstood the issue here?! (which could very well be *lol*)

Jonathan Snook said on July 13, 2006

Karinne: that's how I've traditionally named my tables but many development frameworks like Cake and Ruby recommend a naming convention where the identifier would just be 'id'.

jlactose: you might be using a cached stylesheet. I updated a little while ago because of that very issue. Force refresh or clear your cache and try again.

Master said on July 13, 2006

Some useful simple alternative to what I used to do! thanks for sharing it Jonathan

Karinne said on July 13, 2006

Ah ... gotcha ... I haven't had time to jump on the Cake and Ruby band-wagons yet! When I do tho ... I will keep this post in mind ;)

Fredrik Wärnsberg said on July 13, 2006

Nice writeup. I should really improve my SQL-knowledge, I only know the basics.

Sorry, comments are closed for this post. If you have any further questions or comments, feel free to send them to me directly.