CODEWITHSUNDEEP
mysqlsql
Olivier Pons
Olivier Pons

Reputation: 15778

mysql: rename all fields of a join

I didn't find anything about that.

Here's my query:

SELECT p.*, pa.*, a.*
FROM produit p
LEFT OUTER JOIN produit_attribut pa
ON p.id=pa.id_produit
LEFT OUTER JOIN attribut a
ON a.id=pa.id_attribut
ORDER BY p.id,a.id;

But the problem is that it return columns 

with the same name:

+----+------------+-----+------------+-------------+------+-----+----+----+
| id | titre      | desc| id_produit | id_attribut | id   | desc| val| abb|
+----+------------+-----+------------+-------------+------+-----+----+----+
|  1 | Anchois    | Sauc|          1 |           1 |    1 | Nomb| 2  | Nb |  
|  2 | Fromage    | Sauc|          2 |           1 |    1 | Nomb| 2  | Nb |  
|  3 | Mozzarella | Sauc|          3 |           1 |    1 | Nomb| 2  | Nb |  
|  4 | Jambon     | Sauc|          4 |           1 |    1 | Nomb| 2  | Nb |  
|  5 | Roquefort  | Sauc|          5 |           1 |    1 | Nomb| 2  | Nb |  
|  6 | Royale     | Sauc|          6 |           1 |    1 | Nomb| 2  | Nb |

I'd like to know if there's a way to rename all fields of a table, something that could look like (I know the following sql doesn't work):

SELECT p.* as p*, pa.* as pa*, a.* as att*
FROM produit p
LEFT OUTER JOIN produit_attribut pa
ON p.id=pa.id_produit
LEFT OUTER JOIN attribut a
ON a.id=pa.id_attribut
ORDER BY p.id,a.id;

By the way I know that I can do something like:

SELECT
  p.id AS p_id,
  p.titre AS p_titre,
  p.description AS p_description,
  p.prix AS p_prix,
  p.text_detail AS p_text_detail,
  p.img_petite AS p_img_petite,
  p.img_grande AS p_img_grande,
  pa.id_produit AS pa_id_produit,
  pa.id_attribut AS pa_id_attribut,
  a.id AS a_id,
  a.description AS a_description,
  a.valeur AS a_valeur,
  a.abbreviation AS a_abbreviation
FROM produit p
LEFT OUTER JOIN produit_attribut pa
ON p.id=pa.id_produit
LEFT OUTER JOIN attribut a
ON a.id=pa.id_attribut
ORDER BY p_id,a_id;

But I'd like to avoid this.

I'd like the request to be generic, because I'll use this request in Php and it's about a generic CRUD component (and nested table).

Is there a way?

Upvotes: 3

Views: 3047

Answers (1)

PatrikAkerstrand
PatrikAkerstrand

Reputation: 45721

No, there is no way to do that. However, you should try to avoid using * anyway to select columns, so with this refactoring, you get rid of both the * and can add aliases to all columns you need to add them two. Two birds with one stone! ;)

Upvotes: 5

Related Questions