Have Geek Brain

SQL left to join auto-aliasing?

I just realized that I would have to start querying my database due to duplicate column names in my join tables. Is there a way to automatically tell SQL to alias all column names so that they are returned prefixed with the table name? Otherwise it seems rather confusing when only a few are aliased. Just trying to be consistent without typing a ton of extra code.

$sql = "SELECT contracts.po_number, contracts.start_date, contracts.end_date, contracts.description, contracts.taa_required, contracts.account_overdue, jobs.id AS jobs_id, jobs.job_number, companies.id AS companies_id, companies.name AS companies_name
    FROM contracts
    LEFT JOIN jobs ON contracts.job_id = jobs.id
    LEFT JOIN companies ON contracts.company_id = companies.id
    WHERE contracts.id = '$id'
    ORDER BY contracts.end_date";
+2


a source to share


4 answers


No, but you can make life a little easier by using table aliases:



SELECT c.po_number, c.start_date, c.end_date, c.description, 
    c.taa_required, c.account_overdue, j.id AS jobs_id, j.job_number, 
    cm.id AS companies_id, cm.name AS companies_name 
FROM contracts c
LEFT JOIN jobs j ON c.job_id = j.id 
LEFT JOIN companies cm ON c.company_id = cm.id 
WHERE c.id = '$id' 
ORDER BY c.end_date
+4


a source


you can use alias tables in your sql statements so you have to write less, but for actually accessing columns from php there is no way to flatten all of them if you want to access them by name.



you can also access indexed columns from php but this is a maintenance nightmare

0


a source


I would recommend to always alias table names. This makes it difficult to read later if you miss the alias.

0


a source


For reference, theres a getcha in MySQL 5.6 (maybe others!)

SELECT * FROM table1 LEFT JOIN table2 ON PKI = FKI

works as expected. but lately I have been mistaken "LEFT" as "LEFY" in the query and it also worked, but with a standard connection! so

SELECT * FROM table1 LEFY JOIN table2 ON PKI = FKI

also works great as any substitution for the word LEFY, so beware of typos that change your query!

0


a source






More articles:


All Articles