combine four table into one new table

Question

SELECT a.delivery_date,
       a.delivery_hour, 
       a.price as EX-ANTE, 
FROM mms_realtime_dispatch_prices_report a

  UNION ALL

SELECT b.delivery_date,
       b.delivery_hour,
       b.price as EX-POST,
FROM mms_realtime_dispatch_prices_report b

  UNION ALL

SELECT c.region,
       c.dem_rtdel,
       c.date,
FROM pub_demand_lwap c;

  UNION ALL

SELECT region,
       report,
       hour,
       SUM(q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11)
FROM pub_markets_bids_and_offers
WHERE delivery date=03/16/2011
GROUP BY hour

help! need to combine this four table into one new table no duplicate data Can you help me in combining this four tables into one table. this is the first time i encounter this. I really need a help :(

Take a look at distinct: http://dev.mysql.com/doc/refman/5.1/en/distinct-optimization.html — Realitätsverlust, Nov 18 '13 at 09:34
For UNION ALL to work, each of your selects should return the same number of elements and of same types.. — Nadeem_MK, Nov 18 '13 at 09:37
http://stackoverflow.com/questions/6965333/mysql-union-distinct — andy, Nov 18 '13 at 09:38
1. Normalize your design. A database table IS NOT a spreadsheet! And date and time should held as a single entity — Strawberry, Nov 18 '13 at 09:50
Can you suggest a SQL code for combining this 4 table into one? i really need a help.. :( — Misty, Nov 18 '13 at 10:57

score 0 · Answer 1 · answered Nov 18 '13 at 09:54

0

In SQL server union works only if there is same number and type of columns return by query.

answered Nov 18 '13 at 09:54

raza rabbani

449
3
12

M.Ali · Answer 2 · 2013-11-19T15:36:12.850

You need to get your Union query right 1st I can see so many things wrong with Query At the moment,

Your number of columns retunred by each select arent same, Last query is returning 4 columns and other 3,
You are Aliasing Columns in 2nd query, it will not have any effect as Only the Column Names from very first select statement are visible in the result set.
Guessing from the Column names you have Different Data Types that you are trying to UNION. Datatypes Returned from all selects that you are using in UNION should return the same datatypes e.g

    SELECT Column1_DataType1, Column2_DataType2, Column3_DataType3 FROM Table_Name1
    UNION ALL
    SELECT Column1_DataType1, Column2_DataType2, Column3_DataType3 FROM Table_Name2
    UNION ALL
    SELECT Column1_DataType1, Column2_DataType2, Column3_DataType3 FROM Table_Name3

and so on....
Once you have met all these condition then you can do something like this to eliminate duplicate rows from you result set

;with CTE 
AS
 (
   SELECT ID_Column, rn = ROW_NUMBER() OVER (PARTITION BY Column1, Column2, Column3... ORDER BY ID ASC)
  FROM ( -- All of your UNION ALL Statements Can go here --)q
)
DELETE FROM CTE 
WHERE rn = 1

hello Sir the idea of this is to select column in that four tables then transfer into the new table, the new table will serve as the final data which i will display. now i'm trying to use insert ignore. another problem in the selected column, i have a display data that i will get in one column then separate into new columns ex: col1: value:a|b|c will become colA| colb| colc and the equivalent value will be price of a|b|c. im having a hard time. can you help me? im a new programmer. :( — Misty, Nov 19 '13 at 03:39

combine four table into one new table

2 Answers2