MySQL Join over multiple tables

Question

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.

Yup, thats got it, I added a secondary WHERE clause to find sites with an ID of 1 and it solved the issue. Thanks :) — bgh
– bgh, Commented Jun 21, 2012 at 9:56

Marco · Accepted Answer · 2012-06-21 09:52:24Z

1

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.

answered Jun 21, 2012 at 9:52

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join over multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related