CODEWITHSUNDEEP
mysqlselectjoin
James Glass
James Glass

Reputation: 4300

Selecting two Fields from two different tables as one (using Join) (MYSQL)

I've got the following SQL, selecting articles in two different ways. All articles are stored in the Articles table. Some are marked as Article Of The Month (Articles_Fitness), while others are marked as purely fitness articles (ArticlesInCategories).

My problem is that I need to select both Articles_Fitness.StartDate and Articles.PublicationDate as the Formatted_Date. Is this possible, or will I need to label one as Formatted_Date1 and Formatted_Date2, and then check when I output it?

SELECT 
       Articles.ArticleID, 
       Articles.Title, 
       Articles.Author, 
       Articles.Abstract, 
       date_format(Articles_Fitness.StartDate, '%M %d, %Y') AS Formatted_Date,
       date_format(Articles.PublicationDate, '%M %d, %Y') AS Formatted_Date
     FROM 
       Articles 
       LEFT JOIN Articles_Fitness
            ON Articles_Fitness.ArticleID = Articles.ArticleID
            AND Articles_Fitness.StartDate <= CURDATE()
       LEFT JOIN ArticlesInCategories 
            ON ArticlesInCategories.ArticleID = Articles.ArticleID
            AND ArticlesInCategories.CategoryID = '1'
     WHERE 
       Articles.Body IS NOT NULL AND
       Articles.Body != '' AND
       Articles.Public ='1'

Upvotes: 0

Views: 100

Answers (1)

pilcrow
pilcrow

Reputation: 58544

Are you saying you want Articles_Fitness.StartDate if present, otherwise Articles.PublicationDate? You can return that in one column:

...
DATE_FORMAT(COALESCE(Articles_Fitness.StartDate, Articles.PublicationDate),
            '%M %d, %Y') AS Formatted_Date
...

Upvotes: 2

Related Questions