0

I recently learned how to use the UNION ALL command. However, I don't know how to use it with a join.

Can someone show me how to modify the query below so that it performs a left join on TWO tables - gw_articles_usa and gw_articles_canada?

$stm = $pdo->prepare("SELECT WT.N, WT.URL, WT.Title,
 USA.URL, USA.Brief, USA.Article, USA.Pagedex, USA.Links
 FROM gw_topics WT
 LEFT JOIN gw_articles_usa USA ON USA.URL = WT.URL
 WHERE WT.URL = :MyURL");
 $stm->execute(array(
 'MyURL'=>$MyURL
 ));
Chris
  • 2,766
  • 1
  • 29
  • 34

1 Answers1

1

Provided the usa and canada tables have the same column names and types, use the union in a subselect.

SELECT WT.N, WT.URL, WT.Title,
    COUNTRY.URL, COUNTRY.Brief, COUNTRY.Article, 
    COUNTRY.Pagedex, COUNTRY.Links
 FROM gw_topics WT
 LEFT JOIN (select * from gw_articles_usa UNION ALL select * from gw_articles_canada) COUNTRY 
    ON COUNTRY.URL = WT.URL
 WHERE WT.URL = :MyURL

If the two tables don't match, you can use aliases for the column names to make them match or use a constant/null for a missing column value and alias to the name of the other table. If data types don't match, CAST or other conversion functions can be used to make them the same. If the links was missing from one of the tables, you can do this.

select  URL, Brief, Article, Pagedex, Links from gw_articles_usa 
UNION ALL 
select URL, Brief, Article, Pagedex, null as Links from gw_articles_canada

This should resolve your problem.

Chris Barlow
  • 466
  • 4
  • 8
  • Yikes - I marked that as the correct answer, but your comment reminded me that the tables aren't exactly the same. Table gw_articles_canada doesn't have the field Links. How can I modify your query to make that work? –  Nov 14 '14 at 01:58