What always bothered me when writing SQL SELECT Statements is handling long table names, and having to reference a field by its table's name to avoid ambiguous errors.
Take this for example, three tables: infobase, infobase_labels, and infobase_users. And let's imagine that those three tables each have a field named 'ID'.
So a simple select statement will go like that:
SELECT infobase.ID, infobase.NAME AS INFO_NAME, infobase_labels.NAME AS LABEL_NAME, PHONE FROM infobase_labels, infobase_users, infobase WHERE infobase_labels.ID=infobase.LABEL_ID and infobase_users.ID=infobase.USER_ID AND infobase.ID=5;
Okay, 238 characters of unreadable sql code.
Whats next? Okay, till here you have two options:
1. Live with it.
2. Find a simpler way.
I have been using option 1 for the last 7 years. Until, option two showed up ...
... the usage of name aliasing for table names to make a query simpler, now this I love, simple alias each table with a 2 letter name, and use it instead of the table name:
SELECT _IB.ID, _IB.NAME AS INFO_NAME, _IBL.NAME AS LABEL_NAME, PHONE FROM infobase_labels AS _IBL, infobase_users AS _IBU, infobase AS _IB WHERE _IBL.ID=_IB.LABEL_ID and _IBU.ID=_IB.USER_ID AND _IB.ID=5;
Okay, this makes them 205 characters long.
For me the latter is much more readable, shorted and easier to write.
Tidying the SQL statement a bit:
SELECT
_IB.ID,
_IB.NAME AS INFO_NAME,
_IBL.NAME AS LABEL_NAME,
PHONE
FROM
infobase_labels AS _IBL,
infobase_users AS _IBU,
infobase AS _IB
WHERE
_IBL.ID=_IB.LABEL_ID
AND _IBU.ID=_IB.USER_ID
AND _IB.ID=5;
Okay thats it for today. Quite a long post, but I like it ; )
