SQL db query: how to add a new field and assign values

query

I have 3 tables as shown in the screenshot below. I have created a query using UNION as shown below.

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS
FROM Table3

And I got the result below.

My question is, how do I add a column (field) called TYPE and assign values to the query results but not updating the existing three tables to make it like the screenshot below?

Best Answer

Not the most elegant of solutions, but to answer your question, you could do this:

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS, 'ANIMAL' AS TYPE
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS, 'HUMAN' AS TYPE
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS, 'OBJECT' AS TYPE
FROM Table3

Related Solutions

Postgresql – Search entire database(210 tables) for a unique Id (PK) and get the table name

In your query:

SELECT table_name 
FROM information_schema.columns 
WHERE (:uniqueid ) IN (SELECT 'Id' 
                       FROM information_schema.columns 
                       WHERE 'Id' = (:uniqueid ))

you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find :uniqueid in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:

c1.execute("SELECT table_name FROM information_schema.columns WHERE column_name = id")
for row in c1.fetchall():
    t = row[0]
    c2.execute("SELECT 1 FROM %s WHERE id = :uniqueid" % (t) )
    if c2.fetchone():
        print "Found %s in %s" %  (:uniqueid, t)
        sys.exit(0)

will print the first table where there is an id = :uniqueid

Another option is to generate a union all for all tables:

SELECT 't1' from t1 where id = :uniqueid
UNION ALL
SELECT 't2' from t2 where id = :uniqueid
UNION ALL
...
UNION ALL 
SELECT 't210' from t210 where id = :uniqueid

Mysql – Handle 4 tables and to show 4 columns as result

First I'll explain the mistakes/errors.

1) UNION provides the vertical merging not horizontal.

+---+---+         +---+---+     +---+---+       
| A | B |         | A | B |     | 1 | 2 |
+---+---+         +---+---+     | 3 | 4 |              +---+---+---+---+
| 1 | 2 |  UNION  | 6 | 7 |  =  | 6 | 7 |          not | 1 | 2 | 6 | 7 |
| 3 | 4 |         | 8 | 9 |     | 8 | 9 |              | 3 | 4 | 8 | 9 |
+---+---+         +---+---+     +---+---+              +---+---+---+---+

Also UNION requires both merged tables have columns of the same names/types/order. That is why you get only two columns while the columns and rows from the second table are dissapeared.

2) That "some syntax error" was the requirement that each tablesource used by query should have the name. When you have used the subquery you should give it an alias:

SELECT SectorTrend.Trend
  FROM ( SELECT Sectors.SectorName
           FROM ( SELECT Companies.Symbol, CompanyData.strenght 
                   --- some missed clause should be here
                   INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
                   WHERE CompanyData.strenght = 'anyvalue'
                ) AS aaa --- that's what I mean
          INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
          WHERE Sectors.SectorID = 'anynumber'
      ) AS bbb --- that's what I mean
 INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

Anyway that query is too complicated and wrong to be used.

3) Multiple JOINs
Your mistake is that you have joined the table Companies twice (instead of Sectors) and engine can't tell one instance from another. The good practice is to assign an unique alias to each table in the query:

SELECT c.Symbol
     , s.SectorName
     , cd.strenght 
     , st.Trend 
  FROM Companies   AS c
  JOIN Sectors     AS s  ON s.SectorID   = c.SectorID
  JOIN CompanyData AS cd ON cd.CompanyID = c.CompanyID
  JOIN SectorTrend AS st ON st.SectorID  = s.SectorID

I've replaced the INNER JOINs by JOINs because (at least in the mysql dialect) they are exactly the same with ON clause. And this query is my choice for the task.

Best Answer

Related Solutions

Postgresql – Search entire database(210 tables) for a unique Id (PK) and get the table name

Mysql – Handle 4 tables and to show 4 columns as result

Related Question