Jump to content

How does MySQL handle duplicate field names in a query


pnj

Recommended Posts

All the tables in my application have a 'creator' field that tracks the user to create the record.

If I run a join query, mysql will have two fields with a 'creator' element.  What gets returned to php?  Can I count on the join always returning the 'creator' field in the first table, or is this behavior unpredictable?

For instance, in

SELECT * FROM book INNER JOIN customer ON whatever...

Will the php array returned from mysql_query() and mysql_fetch_array() necessarily contain book.creator as its 'creator' element?

Thanks
-pnj
Link to comment
Share on other sites

use a alias, so you return exactly what you want. Anytime you do a JOIN and need a certain table column value when there could be duplicates that may or may not contain the same value and you need to return the specific table column, then to refine the JOIN, use table aliases so you keep your select options open.

As a side note note MySql depending on your versions, treats the same JOIN differently depending on your version. In later version of MySQL 5, many of the JOIN types have been changed to follow the standard SQL JOIN reasoning.

By default INNER JOIN, joins all columns from the second table to the first table, using table aliases allows you to restrict what is returned.

[code]SELECT ta.name AS book_name, tb.name AS buyer_name FROM book AS ta INNER JOIN customer AS tb ON(...[/code]

INNER JOIN = CROSS JOIN (MySQL)

You use INNER JOIN when you use ON(), you use CROSS JOIN when don't use ON() in your query (SQL standard)

printf
Link to comment
Share on other sites

Thanks printf,

I follow on the field aliases, but I don't follow what you mean on the table aliases.

What is the difference between:
[code]SELECT ta.name AS book_name, tb.name AS buyer_name FROM book AS ta INNER JOIN customer AS tb ON(...[/code]

and

[code]SELECT book.name AS book_name, customer.name AS buyer_name FROM book INNER JOIN customer ON(...[/code]

i.e. how does table aliasing help me?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.