Query within Query

I couldn’t find the way how to write a query within another query result. I created four tables, and as you can see the “article #3” isn’t assigned to any writer in ‘writers_articles’ table. I struggle to write a select query where it will pull <td>Public Domain</td> even it didn’t exist in the ‘writers_articles’ table.


(table - 'articles')

articles_id    articles_symbol
----------------------------------
1              SymbolOne
2              SymbolTwo
3              SymbolThree


(table - 'articles_descriptions')

articles_id    articles_name
----------------------------------
1              ArticleNameOne
2              ArticleNameTwo
3              ArticleNameThree


(table - 'writers')

writers_id     writers_name
----------------------------------
1              John
2              Kelly


(table - 'writers_articles')

writers_id     articles_id
----------------------------------
1              1
2              2



$select = 'SELECT DISTINCT ad.articles_id, ad.articles_name, a.articles_id';
$from   = ' FROM ' . TABLE_ARTICLES_DESCRIPTION . ' ad, ' . TABLE_ARTICLES . ' a';
$where  = ' WHERE 1=1 AND ad.articles_id = a.articles_id';
$order_by  = 'ORDER BY ad.articles_name ASC';

$select2 = 'SELECT DISTINCT ad.articles_id, ad.articles_name, a.articles_id, a.articles_symbol';
$from2   = 'FROM ' . TABLE_ARTICLES_DESCRIPTION . ' ad, ' . TABLE_ARTICLES . ' a, ' . TABLE_WRITERS . ' w, ' . TABLE_WRITERS_ARTICLES . ' wa';
$where2  = 'WHERE 1=1 AND ad.articles_id = a.articles_id AND a.articles_id = wa.articles_id AND w.writers_id = wa.writers_id';

$articles = mysql_query($select . $from . $where . $order_by);
	while ($article = mysql_fetch_array($articles)) {
		echo '
			<tr>
				<td>' . $article['articles_name'] . '</td>';
	if (mysql_query) {
	$writers = mysql_query($select2 . $from2 . $where2);
		while ($writer = mysql_fetch_array($writers)) {
	if (mysql_query) {
		echo '
			<td>' . $writer['writer_name'] . '</td>';
		} else {
		echo '
			<td>Public Domain</td>';
		}
	}
		echo '</tr>';
}
}

Please help me if you can.

R937,

Thank you so much! I really appreciate your time to help me. :wink:

SELECT a.articles_id
     , a.articles_symbol
     , ad.articles_name
     , [COLOR="red"]COALESCE(w.writers_name,'Public Domain') AS writer[/COLOR]
  FROM articles AS a
INNER
  JOIN articles_descriptions AS ad
    ON ad.articles_id = a.articles_id
[COLOR="Red"]LEFT OUTER[/COLOR]
  JOIN writers_articles AS wa
    ON wa.articles_id = a.articles_id
[COLOR="red"]LEFT OUTER[/COLOR]
  JOIN writers AS w
    ON w.writers_id = wa.writers_id
ORDER 
    BY ad.articles_name ASC

:slight_smile: