pnj Posted January 3, 2007 Share Posted January 3, 2007 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 Quote Link to comment Share on other sites More sharing options...
printf Posted January 3, 2007 Share Posted January 3, 2007 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 Quote Link to comment Share on other sites More sharing options...
pnj Posted January 3, 2007 Author Share Posted January 3, 2007 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? Quote Link to comment Share on other sites More sharing options...
craygo Posted January 3, 2007 Share Posted January 3, 2007 you can also use the entire field name[code]SELECT * FROM book JOIN customer ON book.creator = customer.creator[/code]Ray Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.