Member Avatar for SimonIoa

Hello i want to UNION three tables from the same database. 

SELECT DISTINCT * 
        FROM
    (SELECT uid as id, name as name, username as username,bio as aboutme, '' as text, 'user' as type, profile_pic as profile_pic, '' as media_pic, '' as group_pic, 'U' as mediatype, '' as image_path FROM users ORDER BY rand() LIMIT 5) AS T

            UNION ALL
            (SELECT group_id as id, group_name as name,'' as username, group_desc as aboutme, '' as text, 'group' as type, '' as profile_pic, '' as media_pic, group_pic as group_pic, 'G' as mediatype, '' as image_path FROM groups ORDER BY rand() LIMIT 5)
        UNION ALL
            (SELECT M.msg_id as id, M.title as name, '' as username,M.description as aboutme, M.message as text, 'media' as type, '' as profile_pic,  M.media_pic as media_pic, '' as group_pic, M.type as mediatype, P.image_path as image_path FROM messages M, user_uploads P ORDER BY rand() LIMIT 5)

It works but i want to return the original names of the rows for example not id but uid or msg_id or group_id separately. But i still need to UNION Thanks in advance

  • 3 Contributors
  • 3 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by SimonIoa
Member Avatar for ryantroop

You cannot change a column name on the fly like this.

You're better off making another column and calling it type and flagging it to know what "type" it is.

Edited by ryantroop
commented: "Oh, SQLfly, You're gonna make your fortune by and by, But if you lose, don't ask no questions why The only game you know is Do or Die" +15
Member Avatar for CesarF

hi Simonloa,

as long as all unioned queries have the same type of columns, you can do a subquery and name result outside it:

select  SQ2.uid as id,
        SQ2.name,
        SQ2.username,
        ...
   from ( select  distinct * 
            from ( select  uid as id...
                    union
                   select  uid as id...
                    union
                   select  uid as id... ) as SQ1 ) as SQ2
  order by SQ2.uid
Member Avatar for SimonIoa

"As long as all unioned queries have the same type of column" You are right @CesarF, i havent expressed myself as i wanted. The problem is that i push the parameters of the array using Angular, to the next page and i need the original names of the columns (group_id,group_name... uid, username... msg_id, title...)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.