CODEWITHSUNDEEP
mysqlsql
prakashchhetri
prakashchhetri

Reputation: 1816

SQL full outer join or Union

I have two different tables. One is a Article table and another is Gallery table. The gallery contains multiple images , so there is another table named images (which is not shown here). The images in the images table link to gallery table with a foreign key gallery_id.

Now what I am trying to achieve is, in the home page, I need the combined result of both the articles and galleries. If its a article, the thumbnail of the article is displayed and if its a gallery, the last image from the gallery is displayed. 

|Article    | 
|-----------|
|id         |
|category_id|
|title      |
|slug       |
|filename   |
|body       |
|created    |
|modified   |

|Gallery|
|-----------|
|id         |
|category_id|
|title      |
|slug       |
|body       |
|created    |
|modified   |

I am using a very complex UNION query to achieve it. But how can I sort the results. Is it possible to use a ORDER BY clause. Can the result be achieved with OUTER JOIN ?

Upvotes: 0

Views: 96

Answers (1)

Brad
Brad

Reputation: 12253

Sounds like outer join does not apply here because you want your results to be in one column. Joins make data in two columns, unions make data into one column.

to do a sort you can do something like this

Select  id         
    , category_id
    , title      
    , slug       
    , filename   
    , body       
    , created    
    , modified
From (  
    Select id         
        , category_id
        , title      
        , slug       
        , filename   
        , body       
        , created    
        , modified 
    From Article 
    Union All Select id         
        , category_id
        , title      
        , slug       
        , body       
        , created    
        , modified 
    From Gallery
) AllCombined
Order by AllCombined.title

Upvotes: 1

Related Questions