WDS 10
Advanced SQL Queries: Aliases
Upon reading ch 9 on "Advanced SQL Queries" in Kevin Yanks book, Build Your Own Database Driven Website with PHP and MYSQL, I became impressed with an in issue that has eluded me, as far as database information retrieval goes. While working with SQL queries with PHP motors, we have assigned names to rows for values in the database for many tables, some of them which are the same. My thought came to this, "What if we request information that is the same from two or three different table but have different assigning values for the request?" In the section, "Column and Table Name Aliases," it became clear to me that there can be an issue but there is a way to organize the results without MYSQL throwing up. To make my concern and question more clear, the book explains a real life issue with an airline company's booking system that tried to list the origin and destination of each flight along with their flight numbers from two different tables. This would be the logical method to query the information for this company:
mysql( SELECT flight.number, city.name, city.name FROM flight, city WHERE flight.origincityid=city.id AND flight.destinationcityid=city.id;)
Logically this would make sense but city.name is named twice and city.id will have to be the same for the designation city and the origin city which actually queries back an empty result because there is not going to be any flight that has the destination and origin the same. While working with PHP motors, we have made many tables that have similar information to each other such as city, state, phone numbers, etc. We have been able to keep them pretty organized by naming the city row in the employee table as emp_city and the city row in the customer table as cus_city but if there were ever a time where we wanted to keep the row names the same for multiple tables, an issue like the one with the airline would likely occur.
The Solution
The solution is to give one of the tables, or both, two different temporary names or aliases for the purpose of the query at hand. We use an additional criteria AS in the FROM portion of the SELECT query. For the example of the airlines, they temporarily gave the name of the city table of origin. The query now looks like this:
mysql( SELECT flight.number, origin.name, destination.name FROM flight, city AS origin, city AS destination WHERE flight.origincityid=origin.id AND flight.destinationcityid=destination.id;)
I hope this makes sense. Here the city rows from the different tables are temporarily given different names to help SQL distinguish that the origin.id and destination.id are for different flights. Assigning an alias can also be done for column names also.
As I continue to build databases and as they become more complicated, I will have to refer to this helpful tool keep queries and my logic is check!
