SQL JOIN : Prefix fields with table name

Question

I have the following tables

CREATE TABLE `constraints` (
  `id` int(11),
  `name` varchar(64),
  `type` varchar(64)
);

CREATE TABLE `groups` (
  `id` int(11),
  `name` varchar(64)
);

CREATE TABLE `constraints_to_group` (
  `groupid` int(11),
  `constraintid` int(11)
);

With the following data :

INSERT INTO `groups` (`id`, `name`) VALUES
(1, 'group1'),
(2, 'group2');

INSERT INTO `constraints` (`id`, `name`, `type`) VALUES
(1, 'cons1', 'eq'),
(2, 'cons2', 'inf');

INSERT INTO `constraints_to_group` (`groupid`, `constraintid`) VALUES
(1, 1),
(1, 2),
(2, 2);

I want to get all constraints for all groups, so I do the following :

SELECT groups.*, t.* FROM groups
LEFT JOIN
    (SELECT * FROM constraints
    LEFT JOIN constraints_to_group
    ON constraints.id=constraints_to_group.constraintid) as t
ON t.groupid=groups.id

And get the following result :

id|  name  | id |  name   type   groupid   constraintid   
-----------------------------------------------------
1 | group1 | 1  | cons1 |  eq  | 1       | 1
1 | group1 | 2  | cons2 | inf  | 1       | 2
2 | group2 | 2  | cons2 | inf  | 2       | 2

What I'd like to get :

group_id | group_name | cons_id |  cons_name | cons_type | groupid | constraintid   
-------------------------------------------------------------------------------------
1        | group1     | 1       | cons1      |  eq       | 1       | 1
1        | group1     | 2       | cons2      | inf       | 1       | 2
2        | group2     | 2       | cons2      | inf       | 2       | 2

This is an example, in my real case my tables have much more columns so using the SELECT groups.name as group_name, ... would lead to queries very hard to maintains.

`SELECT *` is 9/10 cases is curse not blessing. Specify your collumn manually or play with dynamic sql (this would be cumbersome ) — Lukasz Szozda, Oct 27 '15 at 09:41
`SELECT a.id as group_id ,a.name as group_name,..... FROM constraints_to_group as a JOIN groups on groups.id=a.groupid JOIN constraints as t on t.id=a.constraintid`?? — A_Sk, Oct 27 '15 at 09:52
@عجمان As I say in the last sentence of my post, I'm searching for a way to prefix the column name without naming them one by one — IggY, Oct 27 '15 at 09:55
you can use dynamic SQL ( stored procedure) to write a query using the Information_schema database. Retrieve all columns of the table you need, then write the query with the name of tables before the column name. — Kobi, Oct 27 '15 at 10:14
Possible duplicate of [In a join, how to prefix all column names with the table it came from](http://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from) — Jayvee, Oct 27 '15 at 10:17

Mukesh Kalgude · Accepted Answer · 2015-10-27T10:01:34.060

1

Try this way

    SELECT groups.id as group_id,  groups.name as group_name ,
          t.id as cons_id,  t.name as cons_name,  t.type as cons_type,
          a.groupid ,  a.constraintid
   FROM constraints_to_group as a
    JOIN groups on groups.id=a.groupid
    JOIN constraints as t on t.id=a.constraintid

edited Oct 27 '15 at 10:01

answered Oct 27 '15 at 09:45

Mukesh Kalgude

4,814
2
17
32

1

It produce the same problem : id | name | id | name | type – IggY Oct 27 '15 at 09:48
Now check it i think alias name to given column – Mukesh Kalgude Oct 27 '15 at 10:02

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

Possible duplicate of this issue

edited May 23 '17 at 12:06

Community

1
1

answered Oct 27 '15 at 10:05

Don

102
1
10

score 0 · Answer 3 · answered Oct 27 '15 at 10:05

The only difference I see are the names of the columns? Use for that mather an AS-statement.

SELECT 
groups.id AS group_id,
groups.name AS group_name,
t.id AS cons_id,
t.name AS cons_name,
t.groupid, t.constraintid
FROM groups
LEFT JOIN
(SELECT * FROM constraints
LEFT JOIN constraints_to_group
ON constraints.id=constraints_to_group.constraintid) as t
ON t.groupid=groups.id

Besides, a better join-construction is:

SELECT G.id AS group_id,
G.name AS group_name,
CG.id AS cons_id,
CG.name AS cons_name,
C.groupid, C.constraintid
FROM constraints_to_group CG
LEFT JOIN constraints C
ON CG.constraintid = C.id
LEFT JOIN groups G
ON CG.groupid = G.id;

SQL JOIN : Prefix fields with table name

3 Answers3