CODEWITHSUNDEEP
sql
ccdavies
ccdavies

Reputation: 1606

Checking against a table before query?

In the following query, how could I do a sub query to find all categories in exp_categories that have a parent_id AND all categories that are a parent, but don't have any children, then use this instead of the AND c.parent_id != '0'?

SELECT c.cat_url_title
FROM exp_channel_titles as t
LEFT JOIN exp_category_posts AS cp ON cp.entry_id = t.entry_id
LEFT JOIN exp_categories AS c ON cp.cat_id = c.cat_id
WHERE t.url_title = 'hummingbird'
AND c.parent_id != '0'
AND c.cat_url_title != 'latest-work'
AND c.cat_url_title != 'best-selling-images' 
LIMIT 1

Upvotes: 0

Views: 31

Answers (1)

The Impaler
The Impaler

Reputation: 48850

Something like this?

SELECT c.cat_url_title
FROM exp_channel_titles as t
LEFT JOIN exp_category_posts AS cp ON cp.entry_id = t.entry_id
LEFT JOIN exp_categories AS c ON cp.cat_id = c.cat_id
WHERE t.url_title = 'hummingbird'
AND c.parent_id in (
  select id from exp_categories where parent_id is not null
  union
  select id from exp_categories where id not in (
    select parent_id from exp_categories
    )
)
AND c.cat_url_title != 'latest-work'
AND c.cat_url_title != 'best-selling-images' 
LIMIT 1

Upvotes: 1

Related Questions