Renaming a Key on a multi-table MySQL SELECT

Question

I'm trying to get the content of 3 different tables.

table A = Is containing our users list, table B = Is returning contracts related to users,table C = Is returning formula details related to the contracts.

In order to make it the right way, I'm using the following multi table request:

SELECT * FROM rscm_students A, rscm_files B, rscm_formulas C
    WHERE B.dossier_status = 0
    AND A.student_agency = :agency
    AND B.file_student_id = A.id
    AND B.file_formula_id = C.id
    AND C.formula_place = 0
    GROUP BY A.student_uniqid
    ORDER BY B.file_date_create";

This is where the whole damn thing become a little complicated. It is returning the correct datas, but as the primary key of every table here is called "id". I can't do some foreach in php. If I got 3 contracts on 1 user, it impossible for me to regroup every contract in the same user array.

I'm still not an expert in SQL, that's why I'm using Phinx to control my database. This is also why my primary keys are named "id".

If you have a good idea, please let me know!

use an inner join ---> http://dev.mysql.com/doc/refman/5.7/en/join.html — The One and Only ChemistryBlob, Sep 06 '16 at 13:03
dont do select *. Use table aliases and for each alias, create a new alias for each `id` — Drew, Sep 06 '16 at 13:03
Also use explicit join syntax versus the old style of *joining* via a from clause — Drew, Sep 06 '16 at 13:04
so the select list would be select A.id as aid, ...., B.id as bid, ... — Drew, Sep 06 '16 at 13:05

score 1 · Accepted Answer · answered Sep 06 '16 at 13:13

Alright, I will make an answer out of it.

First off, don't use

select *

The above select is fine for quick and dirty development prior to production. But it makes a mess out of things such as your joins with common column names coming out of multiple tables (like id and others).

Use modern explicit join syntax. Don't use the older join style. So use join and on.

Lastly with table aliases, create unique output column names for the id columns or other clashes such as

A.id as aid, B.id as bid

`SELECT A.*, B.*, C.*, A.id AS student_id, B.id AS dossier_id, C.id AS formule_id ` — ElBigZob, Sep 07 '16 at 16:17

Renaming a Key on a multi-table MySQL SELECT

1 Answers1