SQL select join is it possible to prefix all columns as prefix

Question

I m wondering if this is possible in SQL  Say you have two tables A and B  and you do a select on table A and join on table B  SELECT a    b   FROM TABLE A a JOIN TABLE B b USING  some id    If table A has columns  a id    name   and  some id   and table B has  b id    name   and  some id   the query will return columns  a id    name    some id    b id    name    some id   Is there any way to prefix the column names of table B without listing every column individually  The equivalent of this  SELECT a    b b id as  b b id   b name as  b name   b some id as  b some id  FROM TABLE A a JOIN TABLE B b USING  some id    But  as mentioned  without listing every column  so something like  SELECT a    b   as  b    FROM TABLE A a JOIN TABLE B b USING  some id    Basically something to say   quot prefix every column returned by b   with  something  quot   Is this possible or am I out of luck  EDITS Advice on not using SELECT   and so on is valid advice but not relevant in my context  so please stick to the problem at hand -- is it possible to add a prefix  a constant specified in the SQL query  to all the column names of a table in a join  My ultimate goal is to be able to do a SELECT   on two tables with a join  and be able to tell  from the names of the columns I get in my result set  which columns came from table A and which columns came from table B  Again  I don t want to have to list columns individually  I need to be able to do a SELECT

User · Answer

Or you could use Red Gate SQL Refactor or SQL Prompt, which expands your SELECT * into column lists with a click of the Tab button

so in your case, if you type in SELECT * FROM A JOIN B ... Go to the end of *, Tab button, voila! you'll see SELECT A.column1, A.column2, .... , B.column1, B.column2 FROM A JOIN B

It's not free though

User · Answer

There is a direct answer to your question for those who use the MySQL C-API   Given the SQL     SELECT a    b    c   FROM table a a JOIN table b b USING  x  JOIN table c c USING  y    The results from  mysql stmt result metadata    gives the definition of your fields from your prepared SQL query into the structure MYSQL FIELD     Each field contains the following data     char  name                     Name of column  may be the alias       char  org name                 Original column name  if an alias      char  table                    Table of column if column was a field      char  org table                Org table name  if table was an alias      char  db                       Database for table      char  catalog                  Catalog for table      char  def                      Default value  set by mysql list fields       unsigned long length           Width of column  create length       unsigned long max length       Max width for selected set      unsigned int name length    unsigned int org name length    unsigned int table length    unsigned int org table length    unsigned int db length    unsigned int catalog length    unsigned int def length    unsigned int flags             Div flags      unsigned int decimals          Number of decimals in field      unsigned int charsetnr         Character set      enum enum field types type     Type of field  See mysql com h for types      Take notice the fields  catalog table org name  You now know which fields in your SQL belongs to which schema  aka catalog  and table  This is enough to generically identify each field from a multi-table sql query  without having to alias anything   An actual product SqlYOG is show to use this exact data in such a manor that they are able to independently update each table of a multi-table join  when the PK fields are present

User · Answer

It seems the answer to your question is no  however one hack you can use is to assign a dummy column to separate each new table  This works especially well if you re looping through a result set for a list of columns in a scripting language such as Python or PHP   SELECT    as table1 dummy  table1       as table2 dummy  table2       as table3 dummy  table3   FROM table1 JOIN table2 ON table2 table1id   table1 id JOIN table3 ON table3 table1id   table1 id   I realize this doesn t answer your question exactly  but if you re a coder this is a great way to separate tables with duplicate column names  Hope this helps somebody

User · Answer

DIfferent database products will give you different answers  but you re setting yourself up for hurt if you carry this very far  You re far better off choosing the columns you want  and giving them your own aliases so the identity of each column is crystal-clear  and you can tell them apart in the results

User · Answer

There is no SQL standard for this   However With code generation  either on demand as the tables are created or altered or at runtime   you can do this quite easily   CREATE TABLE  dbo   stackoverflow 329931 a        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 a  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   CREATE TABLE  dbo   stackoverflow 329931 b        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 b  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   DECLARE  table1 name AS varchar 255  DECLARE  table1 prefix AS varchar 255  DECLARE  table2 name AS varchar 255  DECLARE  table2 prefix AS varchar 255  DECLARE  join condition AS varchar 255  SET  table1 name    stackoverflow 329931 a  SET  table1 prefix    a   SET  table2 name    stackoverflow 329931 b  SET  table2 prefix    b   SET  join condition    a  id    b  id    DECLARE  CRLF AS varchar 2  SET  CRLF   CHAR 13    CHAR 10   DECLARE  a columnlist AS varchar MAX  DECLARE  b columnlist AS varchar MAX  DECLARE  sql AS varchar MAX   SELECT  a columnlist   COALESCE  a columnlist    CRLF               a      COLUMN NAME      AS       table1 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table1 name ORDER BY ORDINAL POSITION  SELECT  b columnlist   COALESCE  b columnlist    CRLF               b      COLUMN NAME      AS       table2 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table2 name ORDER BY ORDINAL POSITION  SET  sql    SELECT      a columnlist           b columnlist     FROM       table1 name      AS a INNER JOIN       table2 name      AS b ON       join condition        PRINT  sql -- EXEC   sql

User · Answer

DIfferent database products will give you different answers  but you re setting yourself up for hurt if you carry this very far  You re far better off choosing the columns you want  and giving them your own aliases so the identity of each column is crystal-clear  and you can tell them apart in the results

User · Answer

I see two possible situations here  First  you want to know if there is a SQL standard for this  that you can use in general regardless of the database  No  there is not  Second  you want to know with regard to a specific dbms product  Then you need to identify it  But I imagine the most likely answer is that you ll get back something like  a id  b id  since that s how you d need to identify the columns in your SQL expression  And the easiest way to find out what the default is  is just to submit such a query and see what you get back  If you want to specify what prefix comes before the dot  you can use  SELECT   FROM a AS my alias   for instance

User · Answer

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I m joining  some of which have the same name ie  id  name  etc    I don t want to list each field  so my solution was to alias those fields that shared a name and use select   for those that have a unique name   For example     table a     id    name    field1    field2      table b     id    name    field3    field4      select a id as aID  a name as aName  a      b id as bID  b name as bName  b             When accessing the results I us the aliased names for these fields and ignore the  original  names   Maybe not the best solution but it works for me    i m use mysql

User · Answer

The only database I know that does this is SQLite  depending on the settings you configure with PRAGMA full column names and PRAGMA short column names   See http   www sqlite org pragma html  Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name  if it s too much trouble for you to type the names of the columns in your query   This is a good example of why it s bad practice to use SELECT   -- because eventually you ll have a need to type out all the column names anyway   I understand the need to support columns that may change name or position  but using wildcards makes that harder  not easier

User · Answer

If concerned about schema changes this might work for you   1  Run a  DESCRIBE table  query on all tables involved    2  Use the returned field names to dynamically construct a string of column names prefixed with your chosen alias

User · Answer

select   usually makes for bad code  as new columns tend to get added or order of columns change in tables quite frequently which usually breaks select   in a very subtle ways   So listing out columns is the right solution   As to how to do your query  not sure about mysql but in sqlserver you could select column names from syscolumns and dynamically build the select clause

User · Answer

Cant do this without aliasing   simply because  how are you going to reference a field in the where clause  if that field exists in the 2 or 3 tables you are joining  It will be unclear for mysql which one you are trying to reference

User · Answer

I implemented a solution based upon the answer suggesting using dummy or sentinel columns in node   You would use it by generating SQL like   select      s          as  prefix  creator      u          as  prefix  speaker      p   from statements s    left join users u on s creator user id   u user id   left join persons p on s speaker person id   p person id   And then post-processing the row you get back from your database driver like addPrefixes row    Implementation  based upon the fields rows returned by my driver  but should be easy to change for other DB drivers    const PREFIX INDICATOR     prefix    const STOP PREFIX INDICATOR     stop prefix       Adds a  lt prefix gt  to all properties that follow a property with the name  PREFIX INDICATOR lt prefix gt     function addPrefixes fields  row      let prefix   null   for  const field of fields        const key   field name     if  key startsWith PREFIX INDICATOR           if  row key                    throw new Error  PREFIX INDICATOR   PREFIX INDICATOR  must not appear with a value  but had value    row key                   prefix   key substr PREFIX INDICATOR length        delete row key        else if  key     STOP PREFIX INDICATOR          if  row key                    throw new Error  STOP PREFIX INDICATOR   STOP PREFIX INDICATOR  must not appear with a value  but had value    row key                   prefix   null       delete row key        else if  prefix          const prefixedKey   prefix   key       row prefixedKey    row key        delete row key              return row     Test   const     addPrefixes    PREFIX INDICATOR    STOP PREFIX INDICATOR      require    BaseDao    describe  addPrefixes        gt      test  adds prefixes        gt        const fields            name   id           name  PREFIX INDICATOR    my prefix            name   foo           name  STOP PREFIX INDICATOR          name   baz              const row           id  1         PREFIX INDICATOR    my prefix               foo   bar          STOP PREFIX INDICATOR             baz   spaz            const expected           id  1        my prefix foo   bar         baz   spaz             expect addPrefixes fields  row   toEqual expected

User · Answer

This question is very useful in practice  It s only necessary to list every explicit columns in software programming  where you pay particular careful to deal with all conditions   Imagine when debugging  or  try to use DBMS as daily office tool  instead of something alterable implementation of specific programmer s abstract underlying infrastructure  we need to code a lot of SQLs  The scenario can be found everywhere  like database conversion  migration  administration  etc  Most of these SQLs will be executed only once and never be used again  give the every column names is just waste of time  And don t forget the invention of SQL isn t only for the programmers use   Usually I will create a utility view with column names prefixed  here is the function in pl pgsql  it s not easy but you can convert it to other procedure languages   -- Create alias-view for specific table   create or replace function mkaview schema varchar  tab varchar  prefix varchar      returns table orig varchar  alias varchar  as    declare     qtab varchar      qview varchar      qcol varchar      qacol varchar      v record      sql varchar      len int  begin     qtab           schema             tab             qview           schema        av     prefix    tab             sql     create view      qview      as select        for v in select   from information schema columns             where table schema   schema and table name   tab     loop         qcol           v column name                 qacol           prefix    v column name                  sql    sql           qcol      as      qacol          sql    sql                   return query select qcol  varchar  qacol  varchar      end loop       len    length sql       sql    left sql  len - 2   -- trim the trailing           sql    sql      from      qtab       raise info  Execute SQL      sql      execute sql  end    language plpgsql    Examples   -- This will create a view  avp person  with  p   prefix to all column names  select   from mkaview  public    person    p      select   from avp person

User · Answer

There are two ways I can think of to make this happen in a reusable way  One is to rename all of your columns with a prefix for the table they have come from  I have seen this many times  but I really don t like it  I find that it s redundant  causes a lot of typing  and you can always use aliases when you need to cover the case of a column name having an unclear origin    The other way  which I would recommend you do in your situation if you are committed to seeing this through  is to create views for each table that alias the table names  Then you join against those views  rather than the tables  That way  you are free to use   if you wish  free to use the original tables with original column names if you wish  and it also makes writing any subsequent queries easier because you have already done the renaming work in the views   Finally  I am not clear why you need to know which table each of the columns came from  Does this matter  Ultimately what matters is the data they contain  Whether UserID came from the User table or the UserQuestion table doesn t really matter  It matters  of course  when you need to update it  but at that point you should already know your schema well enough to determine that

User · Answer

If concerned about schema changes this might work for you   1  Run a  DESCRIBE table  query on all tables involved    2  Use the returned field names to dynamically construct a string of column names prefixed with your chosen alias

User · Answer

I see two possible situations here  First  you want to know if there is a SQL standard for this  that you can use in general regardless of the database  No  there is not  Second  you want to know with regard to a specific dbms product  Then you need to identify it  But I imagine the most likely answer is that you ll get back something like  a id  b id  since that s how you d need to identify the columns in your SQL expression  And the easiest way to find out what the default is  is just to submit such a query and see what you get back  If you want to specify what prefix comes before the dot  you can use  SELECT   FROM a AS my alias   for instance

User · Answer

This question is very useful in practice  It s only necessary to list every explicit columns in software programming  where you pay particular careful to deal with all conditions   Imagine when debugging  or  try to use DBMS as daily office tool  instead of something alterable implementation of specific programmer s abstract underlying infrastructure  we need to code a lot of SQLs  The scenario can be found everywhere  like database conversion  migration  administration  etc  Most of these SQLs will be executed only once and never be used again  give the every column names is just waste of time  And don t forget the invention of SQL isn t only for the programmers use   Usually I will create a utility view with column names prefixed  here is the function in pl pgsql  it s not easy but you can convert it to other procedure languages   -- Create alias-view for specific table   create or replace function mkaview schema varchar  tab varchar  prefix varchar      returns table orig varchar  alias varchar  as    declare     qtab varchar      qview varchar      qcol varchar      qacol varchar      v record      sql varchar      len int  begin     qtab           schema             tab             qview           schema        av     prefix    tab             sql     create view      qview      as select        for v in select   from information schema columns             where table schema   schema and table name   tab     loop         qcol           v column name                 qacol           prefix    v column name                  sql    sql           qcol      as      qacol          sql    sql                   return query select qcol  varchar  qacol  varchar      end loop       len    length sql       sql    left sql  len - 2   -- trim the trailing           sql    sql      from      qtab       raise info  Execute SQL      sql      execute sql  end    language plpgsql    Examples   -- This will create a view  avp person  with  p   prefix to all column names  select   from mkaview  public    person    p      select   from avp person

User · Answer

What I do is use Excel to concatenate the procedure   For instance  first I select   and get all of the columns  paste them in Excel   Then write out the code I need to surround the  column  Say i needed to ad prev to a bunch of columns   I d have my fields in the a column and  as prev   in column B and my fields again in column c   In column d i d have a column     Then use concatanate in column e and merge them together  making sure to include spaces   Then cut and paste this into your sql code  I ve also used this method to make case statements for the same field and other longer codes i need to do for each field in a multihundred field table

User · Answer

The only database I know that does this is SQLite  depending on the settings you configure with PRAGMA full column names and PRAGMA short column names   See http   www sqlite org pragma html  Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name  if it s too much trouble for you to type the names of the columns in your query   This is a good example of why it s bad practice to use SELECT   -- because eventually you ll have a need to type out all the column names anyway   I understand the need to support columns that may change name or position  but using wildcards makes that harder  not easier

User · Answer

In postgres  I use the json functions to instead return json objects     then  after querying  I json decode the fields with a  json suffix   IE   select row to json tab1    tab1 json  row to json tab2    tab2 json   from tab1  join tab2 on tab2 t1id tab1 id   then in PHP  or any other language   I loop through the returned columns and json decode   them if they have the   json  suffix  also removing the suffix  In the end  I get an object called  tab1  that includes all tab1 fields  and another called  tab2  that includes all tab2 fields

User · Answer

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I m joining  some of which have the same name ie  id  name  etc    I don t want to list each field  so my solution was to alias those fields that shared a name and use select   for those that have a unique name   For example     table a     id    name    field1    field2      table b     id    name    field3    field4      select a id as aID  a name as aName  a      b id as bID  b name as bName  b             When accessing the results I us the aliased names for these fields and ignore the  original  names   Maybe not the best solution but it works for me    i m use mysql

User · Answer

I see two possible situations here  First  you want to know if there is a SQL standard for this  that you can use in general regardless of the database  No  there is not  Second  you want to know with regard to a specific dbms product  Then you need to identify it  But I imagine the most likely answer is that you ll get back something like  a id  b id  since that s how you d need to identify the columns in your SQL expression  And the easiest way to find out what the default is  is just to submit such a query and see what you get back  If you want to specify what prefix comes before the dot  you can use  SELECT   FROM a AS my alias   for instance

User · Answer

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I m joining  some of which have the same name ie  id  name  etc    I don t want to list each field  so my solution was to alias those fields that shared a name and use select   for those that have a unique name   For example     table a     id    name    field1    field2      table b     id    name    field3    field4      select a id as aID  a name as aName  a      b id as bID  b name as bName  b             When accessing the results I us the aliased names for these fields and ignore the  original  names   Maybe not the best solution but it works for me    i m use mysql

User · Answer

PHP 7 2   MySQL Mariadb  MySQL will send you multiple fields with the same name  Even in the terminal client  But if you want an associative array  you ll have to make the keys yourself   Thanks to  axelbrz for the original  I ve ported it to newer php and cleaned it up a little   function mysqli rows with columns  link   query         result   mysqli query  link   query       if    result            return mysqli error  link              field count   mysqli num fields  result        fields   array        for   i   0   i  lt   field count   i               field   mysqli fetch field direct  result   i            fields      field- gt table          field- gt name    changed by AS           fields      field- gt orgtable          field- gt orgname    actual table field names            rows   array        while   row   mysqli fetch row  result              new row   array            for   i   0   i  lt   field count   i                   new row  fields  i      row  i                      rows      new row            mysqli free result  result       return  rows      link   mysqli connect  localhost    fixme    fixme    fixme    print r mysqli rows with columns  link   select foo    bar   from foo  bar

User · Answer

I totally understand why this is necessary - at least for me it s handy during rapid prototyping when there are a lot of tables necessary to be joined  including many inner joins  As soon as a column name is the same in a second  joinedtable    field wild card  the main table s field values are overriden with the joinedtable values  Error prone  frustrating and a violation of DRY when having to manually specify the table fields with aliases over and over     Here is a PHP  Wordpress  function to achieve this through code generation together with an example of how to use it  In the example  it is used to rapidly generate a custom query that will provide the fields of a related wordpress post that was referenced through a advanced custom fields field   function prefixed table fields wildcard  table   alias        global  wpdb       columns    wpdb- gt get results  SHOW COLUMNS FROM  table   ARRAY A         field names   array        foreach   columns as  column                 field names      column  Field               prefixed   array        foreach   field names as  field name                 prefixed         alias      field name   AS    alias    field name                return implode        prefixed      function test prefixed table fields wildcard         global  wpdb        query         SELECT             prefixed table fields wildcard  wpdb- gt posts   campaigns                    prefixed table fields wildcard  wpdb- gt posts   venues               FROM  wpdb- gt posts AS campaigns     LEFT JOIN  wpdb- gt postmeta meta1 ON  meta1 meta key    venue  AND campaigns ID   meta1 post id      LEFT JOIN  wpdb- gt posts venues ON  venues post status    publish  AND venues post type    venue  AND venues ID   meta1 meta value      WHERE 1     AND campaigns post status    publish      AND campaigns post type    campaign      LIMIT 1             echo   lt pre gt  query lt  pre gt          posts    wpdb- gt get results  query  OBJECT        echo   lt pre gt        print r  posts       echo   lt  pre gt        The output   SELECT      campaigns   ID  AS  campaigns ID    campaigns   post author  AS  campaigns post author    campaigns   post date  AS  campaigns post date    campaigns   post date gmt  AS  campaigns post date gmt    campaigns   post content  AS  campaigns post content    campaigns   post title  AS  campaigns post title    campaigns   post excerpt  AS  campaigns post excerpt    campaigns   post status  AS  campaigns post status    campaigns   comment status  AS  campaigns comment status    campaigns   ping status  AS  campaigns ping status    campaigns   post password  AS  campaigns post password    campaigns   post name  AS  campaigns post name    campaigns   to ping  AS  campaigns to ping    campaigns   pinged  AS  campaigns pinged    campaigns   post modified  AS  campaigns post modified    campaigns   post modified gmt  AS  campaigns post modified gmt    campaigns   post content filtered  AS  campaigns post content filtered    campaigns   post parent  AS  campaigns post parent    campaigns   guid  AS  campaigns guid    campaigns   menu order  AS  campaigns menu order    campaigns   post type  AS  campaigns post type    campaigns   post mime type  AS  campaigns post mime type    campaigns   comment count  AS  campaigns comment count        venues   ID  AS  venues ID    venues   post author  AS  venues post author    venues   post date  AS  venues post date    venues   post date gmt  AS  venues post date gmt    venues   post content  AS  venues post content    venues   post title  AS  venues post title    venues   post excerpt  AS  venues post excerpt    venues   post status  AS  venues post status    venues   comment status  AS  venues comment status    venues   ping status  AS  venues ping status    venues   post password  AS  venues post password    venues   post name  AS  venues post name    venues   to ping  AS  venues to ping    venues   pinged  AS  venues pinged    venues   post modified  AS  venues post modified    venues   post modified gmt  AS  venues post modified gmt    venues   post content filtered  AS  venues post content filtered    venues   post parent  AS  venues post parent    venues   guid  AS  venues guid    venues   menu order  AS  venues menu order    venues   post type  AS  venues post type    venues   post mime type  AS  venues post mime type    venues   comment count  AS  venues comment count      FROM wp posts AS campaigns LEFT JOIN wp postmeta meta1 ON  meta1 meta key    venue  AND campaigns ID   meta1 post id  LEFT JOIN wp posts venues ON  venues post status    publish  AND venues post type    venue  AND venues ID   meta1 meta value  WHERE 1 AND campaigns post status    publish  AND campaigns post type    campaign  LIMIT 1  Array        0    gt  stdClass Object                        campaigns ID    gt  33              campaigns post author    gt  2              campaigns post date    gt  2012-01-16 19 19 10              campaigns post date gmt    gt  2012-01-16 19 19 10              campaigns post content    gt  Lorem ipsum              campaigns post title    gt  Lorem ipsum              campaigns post excerpt    gt                campaigns post status    gt  publish              campaigns comment status    gt  closed              campaigns ping status    gt  closed              campaigns post password    gt                campaigns post name    gt  lorem-ipsum              campaigns to ping    gt                campaigns pinged    gt                campaigns post modified    gt  2012-01-16 21 01 55              campaigns post modified gmt    gt  2012-01-16 21 01 55              campaigns post content filtered    gt                campaigns post parent    gt  0              campaigns guid    gt  http   example com  p 33              campaigns menu order    gt  0              campaigns post type    gt  campaign              campaigns post mime type    gt                campaigns comment count    gt  0              venues ID    gt  84              venues post author    gt  2              venues post date    gt  2012-01-16 20 12 05              venues post date gmt    gt  2012-01-16 20 12 05              venues post content    gt  Lorem ipsum dolor sit amet  consectetur adipisicing elit  sed do eiusmod tempor incididunt ut labore et dolore magna aliqua  Ut enim ad minim veniam  quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat  Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur  Excepteur sint occaecat cupidatat non proident  sunt in culpa qui officia deserunt mollit anim id est laborum               venues post title    gt  Lorem ipsum venue              venues post excerpt    gt                venues post status    gt  publish              venues comment status    gt  closed              venues ping status    gt  closed              venues post password    gt                venues post name    gt  lorem-ipsum-venue              venues to ping    gt                venues pinged    gt                venues post modified    gt  2012-01-16 20 53 37              venues post modified gmt    gt  2012-01-16 20 53 37              venues post content filtered    gt                venues post parent    gt  0              venues guid    gt  http   example com  p 84              venues menu order    gt  0              venues post type    gt  venue              venues post mime type    gt                venues comment count    gt  0

User · Answer

There is no SQL standard for this   However With code generation  either on demand as the tables are created or altered or at runtime   you can do this quite easily   CREATE TABLE  dbo   stackoverflow 329931 a        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 a  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   CREATE TABLE  dbo   stackoverflow 329931 b        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 b  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   DECLARE  table1 name AS varchar 255  DECLARE  table1 prefix AS varchar 255  DECLARE  table2 name AS varchar 255  DECLARE  table2 prefix AS varchar 255  DECLARE  join condition AS varchar 255  SET  table1 name    stackoverflow 329931 a  SET  table1 prefix    a   SET  table2 name    stackoverflow 329931 b  SET  table2 prefix    b   SET  join condition    a  id    b  id    DECLARE  CRLF AS varchar 2  SET  CRLF   CHAR 13    CHAR 10   DECLARE  a columnlist AS varchar MAX  DECLARE  b columnlist AS varchar MAX  DECLARE  sql AS varchar MAX   SELECT  a columnlist   COALESCE  a columnlist    CRLF               a      COLUMN NAME      AS       table1 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table1 name ORDER BY ORDINAL POSITION  SELECT  b columnlist   COALESCE  b columnlist    CRLF               b      COLUMN NAME      AS       table2 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table2 name ORDER BY ORDINAL POSITION  SET  sql    SELECT      a columnlist           b columnlist     FROM       table1 name      AS a INNER JOIN       table2 name      AS b ON       join condition        PRINT  sql -- EXEC   sql

User · Answer

The only database I know that does this is SQLite  depending on the settings you configure with PRAGMA full column names and PRAGMA short column names   See http   www sqlite org pragma html  Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name  if it s too much trouble for you to type the names of the columns in your query   This is a good example of why it s bad practice to use SELECT   -- because eventually you ll have a need to type out all the column names anyway   I understand the need to support columns that may change name or position  but using wildcards makes that harder  not easier

User · Answer

There is no SQL standard for this   However With code generation  either on demand as the tables are created or altered or at runtime   you can do this quite easily   CREATE TABLE  dbo   stackoverflow 329931 a        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 a  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   CREATE TABLE  dbo   stackoverflow 329931 b        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 b  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   DECLARE  table1 name AS varchar 255  DECLARE  table1 prefix AS varchar 255  DECLARE  table2 name AS varchar 255  DECLARE  table2 prefix AS varchar 255  DECLARE  join condition AS varchar 255  SET  table1 name    stackoverflow 329931 a  SET  table1 prefix    a   SET  table2 name    stackoverflow 329931 b  SET  table2 prefix    b   SET  join condition    a  id    b  id    DECLARE  CRLF AS varchar 2  SET  CRLF   CHAR 13    CHAR 10   DECLARE  a columnlist AS varchar MAX  DECLARE  b columnlist AS varchar MAX  DECLARE  sql AS varchar MAX   SELECT  a columnlist   COALESCE  a columnlist    CRLF               a      COLUMN NAME      AS       table1 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table1 name ORDER BY ORDINAL POSITION  SELECT  b columnlist   COALESCE  b columnlist    CRLF               b      COLUMN NAME      AS       table2 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table2 name ORDER BY ORDINAL POSITION  SET  sql    SELECT      a columnlist           b columnlist     FROM       table1 name      AS a INNER JOIN       table2 name      AS b ON       join condition        PRINT  sql -- EXEC   sql

User · Answer

There is no SQL standard for this   However With code generation  either on demand as the tables are created or altered or at runtime   you can do this quite easily   CREATE TABLE  dbo   stackoverflow 329931 a        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 a  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   CREATE TABLE  dbo   stackoverflow 329931 b        id   int  IDENTITY 1 1  NOT NULL       col2   nchar  10  NULL       col3   nchar  10  NULL       col4   nchar  10  NULL   CONSTRAINT  PK stackoverflow 329931 b  PRIMARY KEY CLUSTERED         id  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY   DECLARE  table1 name AS varchar 255  DECLARE  table1 prefix AS varchar 255  DECLARE  table2 name AS varchar 255  DECLARE  table2 prefix AS varchar 255  DECLARE  join condition AS varchar 255  SET  table1 name    stackoverflow 329931 a  SET  table1 prefix    a   SET  table2 name    stackoverflow 329931 b  SET  table2 prefix    b   SET  join condition    a  id    b  id    DECLARE  CRLF AS varchar 2  SET  CRLF   CHAR 13    CHAR 10   DECLARE  a columnlist AS varchar MAX  DECLARE  b columnlist AS varchar MAX  DECLARE  sql AS varchar MAX   SELECT  a columnlist   COALESCE  a columnlist    CRLF               a      COLUMN NAME      AS       table1 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table1 name ORDER BY ORDINAL POSITION  SELECT  b columnlist   COALESCE  b columnlist    CRLF               b      COLUMN NAME      AS       table2 prefix   COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table2 name ORDER BY ORDINAL POSITION  SET  sql    SELECT      a columnlist           b columnlist     FROM       table1 name      AS a INNER JOIN       table2 name      AS b ON       join condition        PRINT  sql -- EXEC   sql

User · Answer

I solved a similar problem of mine by renaming the fields in the tables involved  Yes  I had the privilege of doing this and understand that everybody may not have it  I added prefix to each field within a table representing the table name  Thus the  SQL posted by OP would remain unchanged -  SELECT a    b   FROM TABLE A a JOIN TABLE B b USING  some id     and still give the expected results - ease of identifying which table the output fields belongs to

User · Answer

I see two possible situations here  First  you want to know if there is a SQL standard for this  that you can use in general regardless of the database  No  there is not  Second  you want to know with regard to a specific dbms product  Then you need to identify it  But I imagine the most likely answer is that you ll get back something like  a id  b id  since that s how you d need to identify the columns in your SQL expression  And the easiest way to find out what the default is  is just to submit such a query and see what you get back  If you want to specify what prefix comes before the dot  you can use  SELECT   FROM a AS my alias   for instance

User · Answer

I solved a similar problem of mine by renaming the fields in the tables involved  Yes  I had the privilege of doing this and understand that everybody may not have it  I added prefix to each field within a table representing the table name  Thus the  SQL posted by OP would remain unchanged -  SELECT a    b   FROM TABLE A a JOIN TABLE B b USING  some id     and still give the expected results - ease of identifying which table the output fields belongs to

User · Answer

What I do is use Excel to concatenate the procedure   For instance  first I select   and get all of the columns  paste them in Excel   Then write out the code I need to surround the  column  Say i needed to ad prev to a bunch of columns   I d have my fields in the a column and  as prev   in column B and my fields again in column c   In column d i d have a column     Then use concatanate in column e and merge them together  making sure to include spaces   Then cut and paste this into your sql code  I ve also used this method to make case statements for the same field and other longer codes i need to do for each field in a multihundred field table

User · Answer

Developing from this solution  this is how I would approach the problem   First create a list of all the AS statements   DECLARE  asStatements varchar 8000   SELECT  asStatements   ISNULL  asStatements              QUOTENAME table name          QUOTENAME column name      AS           table name         column name       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    TABLE A  OR TABLE NAME    TABLE B  ORDER BY ORDINAL POSITION   Then use it in your query   EXEC  SELECT      asStatements     FROM TABLE A a JOIN TABLE B b USING  some id       However  this might need modifications because something similar is only tested in SQL Server  But this code doesn t exactly work in SQL Server because USING is not supported   Please comment if you can test correct this code for e g  MySQL

User · Answer

Or you could use Red Gate SQL Refactor or SQL Prompt, which expands your SELECT * into column lists with a click of the Tab button

so in your case, if you type in SELECT * FROM A JOIN B ... Go to the end of *, Tab button, voila! you'll see SELECT A.column1, A.column2, .... , B.column1, B.column2 FROM A JOIN B

It's not free though

User · Answer

It seems the answer to your question is no  however one hack you can use is to assign a dummy column to separate each new table  This works especially well if you re looping through a result set for a list of columns in a scripting language such as Python or PHP   SELECT    as table1 dummy  table1       as table2 dummy  table2       as table3 dummy  table3   FROM table1 JOIN table2 ON table2 table1id   table1 id JOIN table3 ON table3 table1id   table1 id   I realize this doesn t answer your question exactly  but if you re a coder this is a great way to separate tables with duplicate column names  Hope this helps somebody

User · Answer

Developing from this solution  this is how I would approach the problem   First create a list of all the AS statements   DECLARE  asStatements varchar 8000   SELECT  asStatements   ISNULL  asStatements              QUOTENAME table name          QUOTENAME column name      AS           table name         column name       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    TABLE A  OR TABLE NAME    TABLE B  ORDER BY ORDINAL POSITION   Then use it in your query   EXEC  SELECT      asStatements     FROM TABLE A a JOIN TABLE B b USING  some id       However  this might need modifications because something similar is only tested in SQL Server  But this code doesn t exactly work in SQL Server because USING is not supported   Please comment if you can test correct this code for e g  MySQL

User · Answer

There are two ways I can think of to make this happen in a reusable way  One is to rename all of your columns with a prefix for the table they have come from  I have seen this many times  but I really don t like it  I find that it s redundant  causes a lot of typing  and you can always use aliases when you need to cover the case of a column name having an unclear origin    The other way  which I would recommend you do in your situation if you are committed to seeing this through  is to create views for each table that alias the table names  Then you join against those views  rather than the tables  That way  you are free to use   if you wish  free to use the original tables with original column names if you wish  and it also makes writing any subsequent queries easier because you have already done the renaming work in the views   Finally  I am not clear why you need to know which table each of the columns came from  Does this matter  Ultimately what matters is the data they contain  Whether UserID came from the User table or the UserQuestion table doesn t really matter  It matters  of course  when you need to update it  but at that point you should already know your schema well enough to determine that

User · Answer

I totally understand why this is necessary - at least for me it s handy during rapid prototyping when there are a lot of tables necessary to be joined  including many inner joins  As soon as a column name is the same in a second  joinedtable    field wild card  the main table s field values are overriden with the joinedtable values  Error prone  frustrating and a violation of DRY when having to manually specify the table fields with aliases over and over     Here is a PHP  Wordpress  function to achieve this through code generation together with an example of how to use it  In the example  it is used to rapidly generate a custom query that will provide the fields of a related wordpress post that was referenced through a advanced custom fields field   function prefixed table fields wildcard  table   alias        global  wpdb       columns    wpdb- gt get results  SHOW COLUMNS FROM  table   ARRAY A         field names   array        foreach   columns as  column                 field names      column  Field               prefixed   array        foreach   field names as  field name                 prefixed         alias      field name   AS    alias    field name                return implode        prefixed      function test prefixed table fields wildcard         global  wpdb        query         SELECT             prefixed table fields wildcard  wpdb- gt posts   campaigns                    prefixed table fields wildcard  wpdb- gt posts   venues               FROM  wpdb- gt posts AS campaigns     LEFT JOIN  wpdb- gt postmeta meta1 ON  meta1 meta key    venue  AND campaigns ID   meta1 post id      LEFT JOIN  wpdb- gt posts venues ON  venues post status    publish  AND venues post type    venue  AND venues ID   meta1 meta value      WHERE 1     AND campaigns post status    publish      AND campaigns post type    campaign      LIMIT 1             echo   lt pre gt  query lt  pre gt          posts    wpdb- gt get results  query  OBJECT        echo   lt pre gt        print r  posts       echo   lt  pre gt        The output   SELECT      campaigns   ID  AS  campaigns ID    campaigns   post author  AS  campaigns post author    campaigns   post date  AS  campaigns post date    campaigns   post date gmt  AS  campaigns post date gmt    campaigns   post content  AS  campaigns post content    campaigns   post title  AS  campaigns post title    campaigns   post excerpt  AS  campaigns post excerpt    campaigns   post status  AS  campaigns post status    campaigns   comment status  AS  campaigns comment status    campaigns   ping status  AS  campaigns ping status    campaigns   post password  AS  campaigns post password    campaigns   post name  AS  campaigns post name    campaigns   to ping  AS  campaigns to ping    campaigns   pinged  AS  campaigns pinged    campaigns   post modified  AS  campaigns post modified    campaigns   post modified gmt  AS  campaigns post modified gmt    campaigns   post content filtered  AS  campaigns post content filtered    campaigns   post parent  AS  campaigns post parent    campaigns   guid  AS  campaigns guid    campaigns   menu order  AS  campaigns menu order    campaigns   post type  AS  campaigns post type    campaigns   post mime type  AS  campaigns post mime type    campaigns   comment count  AS  campaigns comment count        venues   ID  AS  venues ID    venues   post author  AS  venues post author    venues   post date  AS  venues post date    venues   post date gmt  AS  venues post date gmt    venues   post content  AS  venues post content    venues   post title  AS  venues post title    venues   post excerpt  AS  venues post excerpt    venues   post status  AS  venues post status    venues   comment status  AS  venues comment status    venues   ping status  AS  venues ping status    venues   post password  AS  venues post password    venues   post name  AS  venues post name    venues   to ping  AS  venues to ping    venues   pinged  AS  venues pinged    venues   post modified  AS  venues post modified    venues   post modified gmt  AS  venues post modified gmt    venues   post content filtered  AS  venues post content filtered    venues   post parent  AS  venues post parent    venues   guid  AS  venues guid    venues   menu order  AS  venues menu order    venues   post type  AS  venues post type    venues   post mime type  AS  venues post mime type    venues   comment count  AS  venues comment count      FROM wp posts AS campaigns LEFT JOIN wp postmeta meta1 ON  meta1 meta key    venue  AND campaigns ID   meta1 post id  LEFT JOIN wp posts venues ON  venues post status    publish  AND venues post type    venue  AND venues ID   meta1 meta value  WHERE 1 AND campaigns post status    publish  AND campaigns post type    campaign  LIMIT 1  Array        0    gt  stdClass Object                        campaigns ID    gt  33              campaigns post author    gt  2              campaigns post date    gt  2012-01-16 19 19 10              campaigns post date gmt    gt  2012-01-16 19 19 10              campaigns post content    gt  Lorem ipsum              campaigns post title    gt  Lorem ipsum              campaigns post excerpt    gt                campaigns post status    gt  publish              campaigns comment status    gt  closed              campaigns ping status    gt  closed              campaigns post password    gt                campaigns post name    gt  lorem-ipsum              campaigns to ping    gt                campaigns pinged    gt                campaigns post modified    gt  2012-01-16 21 01 55              campaigns post modified gmt    gt  2012-01-16 21 01 55              campaigns post content filtered    gt                campaigns post parent    gt  0              campaigns guid    gt  http   example com  p 33              campaigns menu order    gt  0              campaigns post type    gt  campaign              campaigns post mime type    gt                campaigns comment count    gt  0              venues ID    gt  84              venues post author    gt  2              venues post date    gt  2012-01-16 20 12 05              venues post date gmt    gt  2012-01-16 20 12 05              venues post content    gt  Lorem ipsum dolor sit amet  consectetur adipisicing elit  sed do eiusmod tempor incididunt ut labore et dolore magna aliqua  Ut enim ad minim veniam  quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat  Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur  Excepteur sint occaecat cupidatat non proident  sunt in culpa qui officia deserunt mollit anim id est laborum               venues post title    gt  Lorem ipsum venue              venues post excerpt    gt                venues post status    gt  publish              venues comment status    gt  closed              venues ping status    gt  closed              venues post password    gt                venues post name    gt  lorem-ipsum-venue              venues to ping    gt                venues pinged    gt                venues post modified    gt  2012-01-16 20 53 37              venues post modified gmt    gt  2012-01-16 20 53 37              venues post content filtered    gt                venues post parent    gt  0              venues guid    gt  http   example com  p 84              venues menu order    gt  0              venues post type    gt  venue              venues post mime type    gt                venues comment count    gt  0

User · Answer

Recently ran into this issue in NodeJS and Postgres   ES6 approach  There aren t any RDBMS features I know of that provide this functionality  so I created an object containing all my fields  e g    const schema     columns    id   another column   yet another column       Defined a reducer to concatenate the strings together with a table name   const prefix    table  columns    gt  columns reduce  previous  column    gt      previous push table         column     AS     table         column     return previous            This returns an array of strings  Call it for each table and combine the results   const columns joined       prefix  tab1  schema columns      prefix  tab2  schema columns      Output the final SQL statement   console log  SELECT     columns joined join          FROM tab1  tab2 WHERE tab1 id   tab2 id

User · Answer

There is a direct answer to your question for those who use the MySQL C-API   Given the SQL     SELECT a    b    c   FROM table a a JOIN table b b USING  x  JOIN table c c USING  y    The results from  mysql stmt result metadata    gives the definition of your fields from your prepared SQL query into the structure MYSQL FIELD     Each field contains the following data     char  name                     Name of column  may be the alias       char  org name                 Original column name  if an alias      char  table                    Table of column if column was a field      char  org table                Org table name  if table was an alias      char  db                       Database for table      char  catalog                  Catalog for table      char  def                      Default value  set by mysql list fields       unsigned long length           Width of column  create length       unsigned long max length       Max width for selected set      unsigned int name length    unsigned int org name length    unsigned int table length    unsigned int org table length    unsigned int db length    unsigned int catalog length    unsigned int def length    unsigned int flags             Div flags      unsigned int decimals          Number of decimals in field      unsigned int charsetnr         Character set      enum enum field types type     Type of field  See mysql com h for types      Take notice the fields  catalog table org name  You now know which fields in your SQL belongs to which schema  aka catalog  and table  This is enough to generically identify each field from a multi-table sql query  without having to alias anything   An actual product SqlYOG is show to use this exact data in such a manor that they are able to independently update each table of a multi-table join  when the PK fields are present

User · Answer

I totally understand your problem about duplicated field names   I needed that too until I coded my own function to solve it  If you are using PHP you can use it  or code yours in the language you are using for if you have this following facilities   The trick here is that mysql field table   returns the table name and mysql field name   the field for each row in the result if it s got with mysql num fields   so you can mix them in a new array   This prefixes all columns     Regards   function mysql rows with columns  query         result   mysql query  query       if    result  return false     mysql error   could be used outside      fields   mysql num fields  result        rows   array        while   row   mysql fetch row  result               newRow   array            for   i 0   i lt  fields   i                   table   mysql field table  result   i                name   mysql field name  result   i                newRow  table          name     row  i                      rows      newRow            mysql free result  result       return  rows

User · Answer

select   usually makes for bad code  as new columns tend to get added or order of columns change in tables quite frequently which usually breaks select   in a very subtle ways   So listing out columns is the right solution   As to how to do your query  not sure about mysql but in sqlserver you could select column names from syscolumns and dynamically build the select clause

User · Answer

In postgres  I use the json functions to instead return json objects     then  after querying  I json decode the fields with a  json suffix   IE   select row to json tab1    tab1 json  row to json tab2    tab2 json   from tab1  join tab2 on tab2 t1id tab1 id   then in PHP  or any other language   I loop through the returned columns and json decode   them if they have the   json  suffix  also removing the suffix  In the end  I get an object called  tab1  that includes all tab1 fields  and another called  tab2  that includes all tab2 fields

User · Answer

I implemented a solution based upon the answer suggesting using dummy or sentinel columns in node   You would use it by generating SQL like   select      s          as  prefix  creator      u          as  prefix  speaker      p   from statements s    left join users u on s creator user id   u user id   left join persons p on s speaker person id   p person id   And then post-processing the row you get back from your database driver like addPrefixes row    Implementation  based upon the fields rows returned by my driver  but should be easy to change for other DB drivers    const PREFIX INDICATOR     prefix    const STOP PREFIX INDICATOR     stop prefix       Adds a  lt prefix gt  to all properties that follow a property with the name  PREFIX INDICATOR lt prefix gt     function addPrefixes fields  row      let prefix   null   for  const field of fields        const key   field name     if  key startsWith PREFIX INDICATOR           if  row key                    throw new Error  PREFIX INDICATOR   PREFIX INDICATOR  must not appear with a value  but had value    row key                   prefix   key substr PREFIX INDICATOR length        delete row key        else if  key     STOP PREFIX INDICATOR          if  row key                    throw new Error  STOP PREFIX INDICATOR   STOP PREFIX INDICATOR  must not appear with a value  but had value    row key                   prefix   null       delete row key        else if  prefix          const prefixedKey   prefix   key       row prefixedKey    row key        delete row key              return row     Test   const     addPrefixes    PREFIX INDICATOR    STOP PREFIX INDICATOR      require    BaseDao    describe  addPrefixes        gt      test  adds prefixes        gt        const fields            name   id           name  PREFIX INDICATOR    my prefix            name   foo           name  STOP PREFIX INDICATOR          name   baz              const row           id  1         PREFIX INDICATOR    my prefix               foo   bar          STOP PREFIX INDICATOR             baz   spaz            const expected           id  1        my prefix foo   bar         baz   spaz             expect addPrefixes fields  row   toEqual expected

User · Answer

Recently ran into this issue in NodeJS and Postgres   ES6 approach  There aren t any RDBMS features I know of that provide this functionality  so I created an object containing all my fields  e g    const schema     columns    id   another column   yet another column       Defined a reducer to concatenate the strings together with a table name   const prefix    table  columns    gt  columns reduce  previous  column    gt      previous push table         column     AS     table         column     return previous            This returns an array of strings  Call it for each table and combine the results   const columns joined       prefix  tab1  schema columns      prefix  tab2  schema columns      Output the final SQL statement   console log  SELECT     columns joined join          FROM tab1  tab2 WHERE tab1 id   tab2 id

User · Answer

There are two ways I can think of to make this happen in a reusable way  One is to rename all of your columns with a prefix for the table they have come from  I have seen this many times  but I really don t like it  I find that it s redundant  causes a lot of typing  and you can always use aliases when you need to cover the case of a column name having an unclear origin    The other way  which I would recommend you do in your situation if you are committed to seeing this through  is to create views for each table that alias the table names  Then you join against those views  rather than the tables  That way  you are free to use   if you wish  free to use the original tables with original column names if you wish  and it also makes writing any subsequent queries easier because you have already done the renaming work in the views   Finally  I am not clear why you need to know which table each of the columns came from  Does this matter  Ultimately what matters is the data they contain  Whether UserID came from the User table or the UserQuestion table doesn t really matter  It matters  of course  when you need to update it  but at that point you should already know your schema well enough to determine that

User · Answer

There are two ways I can think of to make this happen in a reusable way  One is to rename all of your columns with a prefix for the table they have come from  I have seen this many times  but I really don t like it  I find that it s redundant  causes a lot of typing  and you can always use aliases when you need to cover the case of a column name having an unclear origin    The other way  which I would recommend you do in your situation if you are committed to seeing this through  is to create views for each table that alias the table names  Then you join against those views  rather than the tables  That way  you are free to use   if you wish  free to use the original tables with original column names if you wish  and it also makes writing any subsequent queries easier because you have already done the renaming work in the views   Finally  I am not clear why you need to know which table each of the columns came from  Does this matter  Ultimately what matters is the data they contain  Whether UserID came from the User table or the UserQuestion table doesn t really matter  It matters  of course  when you need to update it  but at that point you should already know your schema well enough to determine that

User · Answer

PHP 7 2   MySQL Mariadb  MySQL will send you multiple fields with the same name  Even in the terminal client  But if you want an associative array  you ll have to make the keys yourself   Thanks to  axelbrz for the original  I ve ported it to newer php and cleaned it up a little   function mysqli rows with columns  link   query         result   mysqli query  link   query       if    result            return mysqli error  link              field count   mysqli num fields  result        fields   array        for   i   0   i  lt   field count   i               field   mysqli fetch field direct  result   i            fields      field- gt table          field- gt name    changed by AS           fields      field- gt orgtable          field- gt orgname    actual table field names            rows   array        while   row   mysqli fetch row  result              new row   array            for   i   0   i  lt   field count   i                   new row  fields  i      row  i                      rows      new row            mysqli free result  result       return  rows      link   mysqli connect  localhost    fixme    fixme    fixme    print r mysqli rows with columns  link   select foo    bar   from foo  bar

User · Answer

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I m joining  some of which have the same name ie  id  name  etc    I don t want to list each field  so my solution was to alias those fields that shared a name and use select   for those that have a unique name   For example     table a     id    name    field1    field2      table b     id    name    field3    field4      select a id as aID  a name as aName  a      b id as bID  b name as bName  b             When accessing the results I us the aliased names for these fields and ignore the  original  names   Maybe not the best solution but it works for me    i m use mysql

User · Answer

The only database I know that does this is SQLite  depending on the settings you configure with PRAGMA full column names and PRAGMA short column names   See http   www sqlite org pragma html  Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name  if it s too much trouble for you to type the names of the columns in your query   This is a good example of why it s bad practice to use SELECT   -- because eventually you ll have a need to type out all the column names anyway   I understand the need to support columns that may change name or position  but using wildcards makes that harder  not easier

User · Answer

Or you could use Red Gate SQL Refactor or SQL Prompt, which expands your SELECT * into column lists with a click of the Tab button

so in your case, if you type in SELECT * FROM A JOIN B ... Go to the end of *, Tab button, voila! you'll see SELECT A.column1, A.column2, .... , B.column1, B.column2 FROM A JOIN B

It's not free though

User · Answer

Or you could use Red Gate SQL Refactor or SQL Prompt, which expands your SELECT * into column lists with a click of the Tab button

so in your case, if you type in SELECT * FROM A JOIN B ... Go to the end of *, Tab button, voila! you'll see SELECT A.column1, A.column2, .... , B.column1, B.column2 FROM A JOIN B

It's not free though

User · Answer

DIfferent database products will give you different answers  but you re setting yourself up for hurt if you carry this very far  You re far better off choosing the columns you want  and giving them your own aliases so the identity of each column is crystal-clear  and you can tell them apart in the results

User · Answer

DIfferent database products will give you different answers  but you re setting yourself up for hurt if you carry this very far  You re far better off choosing the columns you want  and giving them your own aliases so the identity of each column is crystal-clear  and you can tell them apart in the results

User · Answer

Cant do this without aliasing   simply because  how are you going to reference a field in the where clause  if that field exists in the 2 or 3 tables you are joining  It will be unclear for mysql which one you are trying to reference

User · Answer

select   usually makes for bad code  as new columns tend to get added or order of columns change in tables quite frequently which usually breaks select   in a very subtle ways   So listing out columns is the right solution   As to how to do your query  not sure about mysql but in sqlserver you could select column names from syscolumns and dynamically build the select clause

User · Answer

I totally understand your problem about duplicated field names   I needed that too until I coded my own function to solve it  If you are using PHP you can use it  or code yours in the language you are using for if you have this following facilities   The trick here is that mysql field table   returns the table name and mysql field name   the field for each row in the result if it s got with mysql num fields   so you can mix them in a new array   This prefixes all columns     Regards   function mysql rows with columns  query         result   mysql query  query       if    result  return false     mysql error   could be used outside      fields   mysql num fields  result        rows   array        while   row   mysql fetch row  result               newRow   array            for   i 0   i lt  fields   i                   table   mysql field table  result   i                name   mysql field name  result   i                newRow  table          name     row  i                      rows      newRow            mysql free result  result       return  rows

User · Answer

select   usually makes for bad code  as new columns tend to get added or order of columns change in tables quite frequently which usually breaks select   in a very subtle ways   So listing out columns is the right solution   As to how to do your query  not sure about mysql but in sqlserver you could select column names from syscolumns and dynamically build the select clause

[sql] SQL select join: is it possible to prefix all columns as 'prefix.*'?

Examples related to sql

Examples related to join