Row wise merge two different table

Question

I have two different tables.One is file and another one is image. If uploaded content is image, then content details are stored in image table. Remaining(like doc,pdf etc) things are stored in file table. Both are identified by folder_id.

Now I want to list both files and images according to given folder_id.

Exapmle:

image table

image_id   description     img_orig        img_sm       folder_id
   1         Image one   img_one.png     img_one.png        1
   2         Image_two   img_two.png     img_two.png        1
   3         Image_three img_three.png   img_three.png      2

File Table

file_id       description       file_name    file_size   folder_id  file_type
    1          file_one          one.txt       11KB            1      text/plain
    2          file_two          two.html       2KB            1      text/html

Expected output table is

  1         Image one   img_one.png     img_one.png         1      image
  2         Image_two   img_two.png     img_two.png         1      image
  1         file_one    one.txt          11KB               1      text/plain
  2         file_two    two.html         2KB                1      text/html

I have tried UNION ALL concept in MYSQL. My snippet is

SELECT image_id as id,description,img_orig as name, img_sm as sm_size,folder_id,CONCAT('image','') as type 
UNION ALL
SELECT folder_id as id,description,file_name as name, file_size as sm_size,folder_id,file_type as type 
WHERE folder_id=1

It is working fine. But my question is have any another solution in this? Thanks in Advance.

possible duplicate of [MySQL - Selecting data from multiple tables all with same structure but different data](http://stackoverflow.com/questions/409705/mysql-selecting-data-from-multiple-tables-all-with-same-structure-but-differen) — Shannon, Mar 07 '14 at 05:28
I did n't get any point from your link. Already I used UNOIN ALL concept here. — Ajitha Ms, Mar 07 '14 at 05:51

user2793390 · Answer 1 · 2014-03-07T05:35:09.863

0

You can do a simple JOIN. You can pull any columns from any table. Just make sure to prefix the columns with table name or an alias as below.

Select I.image_id, I.description as img_desc, 
F.description as file_desc, F.folder_id
From image I, file F
where I.folder_id = F.folder_id

edited Mar 07 '14 at 05:35

answered Mar 07 '14 at 05:16

user2793390

741
7
29

I have tried your snippet. But only you are select values from image table. I want to fetch values from file table also – Ajitha Ms Mar 07 '14 at 05:24
Basically I think JOIN is used for combine columns of tables – Ajitha Ms Mar 07 '14 at 05:27
1

@AjithaMs In that case, I can't think of anything else other than UNION. – user2793390 Mar 07 '14 at 05:38

Row wise merge two different table

1 Answers1