SQL - Merging fields from two tables into one, including duplicates

srinath.a · #1 (**permalink**) 09-04-08, 03:29

Hi,

I have two tables, nutritions and food_options.
nutritions has two fields, fooditem_id, taken and .
food_options has two fields, fooditem_id and taken.
fooditem_id in nutritions and food_options will point to id in food_items table(third table).

I want to select all records from nutritions and food_options into a new table, C with Fields fooditem_id and taken. This means I need to combine the values of fooditem_id in nutritions and fooditem_id in food_options into one field (fooditem_id), including the duplicates in both tables.

EX:
nutritions
id fooditem_id taken
1 23 1
2 23 1

food_options
id fooditem_id taken
1 34 1

C
id fooditem_id taken
1 23 1
2 23 1
3 34 1

How can I do this?

thanks,
srinath

r937 · #2 (**permalink**) 09-04-08, 05:22

Code:

INSERT
  INTO C
     ( fooditem_id, taken )
SELECT fooditem_id, taken
  FROM nutritions
UNION ALL
SELECT fooditem_id, taken
  FROM food_options

srinath.a · #3 (**permalink**) 09-04-08, 05:45

Hi r937,
Thanks a lot for the Reply
its working perfectly when used UNION ALL, i was trying using UNION and not getting from the last 3 days and finally you helped me this time

thanks again for your Help

Regards,
Srinath.

Internet Services

Web Development

Digital Marketing

Consumer Tech