CODEWITHSUNDEEP
phpmysqlleft-join
bgh
bgh

Reputation: 99

MySQL Join over multiple tables

I am trying to achieve dynamic META information so that it will allow META information to be inserted depending on the page that you are viewing.

I Have 3 tables

CATEGORIES
  category_id,
  category_name,
  category_title,
  category_url,
  site_id,

SITE
  site_id,
  site_name,
  site_url,
  meta_kw,
  meta_desc,

CATEGORY_META
  site_id,
  category_id,
  meta_keywords,
  meta_description,

I am trying to run a query that allows for the population of the META information from the SITE table if no information exists inside the CATEGORY_META table. To achieve this I am looking at the "category_url" that exists and gleaning the "category_id" from that to run a subquery. However it is returning an empty row which I do not want and is causing errors.

My Query looks like this:

SELECT category_id, s.meta_kw, s.meta_desc, m.meta_keywords, m.meta_description
FROM cms_sites s
LEFT JOIN cms_category_meta m
ON s.site_id=m.site_id AND m.category_id = 
(SELECT category_id FROM cms_categories WHERE category_url='?')

which returns:

 |category_id  |meta_kw  |meta_Desc | meta_keywords  |meta_description  |
 |NULL         |kw       |desc      |NULL            |NULL              |
 |NULL         |         |          |NULL            |NULL              |

I am relatively new to MySQL and any help would be greatly appreciated.

Upvotes: 0

Views: 508

Answers (1)

Marco
Marco

Reputation: 57593

Try this:

SELECT 
  category_id, s.meta_kw, s.meta_desc, m.meta_keywords, m.meta_description
FROM cms_sites s 
LEFT JOIN cms_category_meta m ON s.site_id = m.site_id 
LEFT JOIN cms_categories cat ON m.category_id = cat.category_id
                            AND cat.category_url = '?'

Anyway, if you have some "empty" row as @jlcd told you, this won't solve you problem.

Upvotes: 1

Related Questions