Pulling in data from related entries.

[hothousegraphix]

First off I apologize for the extremely vague title, not too sure how to describe what I'm up against in a short phrase.

I have a series of 3 queries I need to display entry data within a category and assigned to one client.

For the most part I've successfully established the first 2 queries with one issue. I need to call all entries with a t.status = 'open' and t.status = 'featured' but I'm not too sure how to.

I've tried AND/OR but this has not worked. Any advice?

*********************************************** 
All Entries for one Client
*********************************************** 
SELECT t.title
	 , t.url_title
	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = "5") as Client
	 // 5 is the value of a dynamic variable
FROM exp_weblog_titles AS t 
LEFT 
  JOIN exp_weblog_data AS d 
	ON t.entry_id = d.entry_id
   AND t.status = 'open'
LEFT  
  JOIN exp_relationships as r
	ON d.entry_id =  r.rel_parent_id
 WHERE r.rel_child_id = '5' // 5 is the value of a dynamic variable 
 ORDER 
    BY t.entry_date 
DESC

*********************************************** 
All Entries for one Client within selected category
*********************************************** 
SELECT t.title
	 , t.url_title
	 , c.cat_url_title
	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = "5") as Client
	 // 5 is the value of a dynamic variable
  FROM exp_categories AS c 
INNER 
  JOIN exp_category_posts AS cp 
	ON cp.cat_id = c.cat_id 
   AND c.cat_url_title = 'corporate_video' 	 // corporate_video is the value of a variable
INNER 
  JOIN exp_weblog_titles AS t 
    ON t.entry_id = cp.entry_id 
   AND t.status = 'open'
LEFT 
  JOIN exp_weblog_data AS d 
	ON t.entry_id = d.entry_id
LEFT 
  JOIN exp_relationships as r
	ON d.entry_id =  r.rel_parent_id
 WHERE r.rel_child_id = '5' // 5 is the value of a dynamic variable
 ORDER 
    BY t.entry_date 
DESC

My trouble is with this last query. I need to show a list of all Clients who have
projects within one category but I only want to list the client name once.

The difficulty is with how the "Client information" is stored within the CMS DB.
I'm dealing with the systems architecture which allows me to create relationships between entries which enables me to pull in data from another entry. These relationships are defined in their own table but the data is stores in different tables.

[hothousegraphix]

I need to call all entries with a t.status = 'open' and t.status = 'featured'

So, it looks like I need to add "OR" to my AND to evaluate multiple values for one variable.

   AND (t.status = 'open' OR t.status = "featured")

Still trying to resolve the last query.

[hothousegraphix]

OK, so my last query, I need to find all entries within a selected "Category" and then group by the "Client Name" and then only return the most recent of that set.

My problem lies with the fact that the "Client Name" is defined via a relationship that uses id's to tie two different entries together.

Starting off I can bring in all my entries within a selected "Category"

SELECT t.title
	 , t.url_title
	 , c.cat_url_title
	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id) as Client
  FROM exp_categories AS c 
INNER 
  JOIN exp_category_posts AS cp 
	ON cp.cat_id = c.cat_id 
   AND c.cat_url_title = 'corporate_video' 
INNER 
  JOIN exp_weblog_titles AS t 
    ON t.entry_id = cp.entry_id 
   AND (t.status = 'open' OR t.status = "featured")
LEFT
  JOIN exp_weblog_data AS d 
	ON t.entry_id = d.entry_id
LEFT 
  JOIN exp_relationships as r
	ON d.entry_id =  r.rel_parent_id
 ORDER 
    BY t.entry_date 
DESC

Works like a charm but now I'd like to tackle showing my client name:

SELECT t.title
	 , t.url_title
	 , c.cat_url_title
	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id) as client
  FROM exp_categories AS c 
INNER 
  JOIN exp_category_posts AS cp 
	ON cp.cat_id = c.cat_id 
   AND c.cat_url_title = 'corporate_video' 
INNER 
  JOIN exp_weblog_titles AS t 
    ON t.entry_id = cp.entry_id 
   AND (t.status = 'open' OR t.status = "featured")
LEFT
  JOIN (SELECT MAX(entry_id) as max 
		  FROM exp_weblog_data 
		 GROUP 
			BY (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_parent_id)) as ids 
// sub query used to get at table that contains client_name 
	ON t.entry_id = ids.max 
LEFT 
  JOIN exp_weblog_data AS d 
	ON t.entry_id = d.entry_id
LEFT 
  JOIN exp_relationships as r
	ON d.entry_id =  r.rel_parent_id
 ORDER 
    BY t.entry_date 
DESC

However, this is showing all data other than the "client name" and because that is not present, I'm not seeing the the LEFT JOIN to my sub-query reducing the data down to just one "client name" within the selected category.

[hothousegraphix]

Is anyone able to assist?

Thanks

[hothousegraphix]

Well, I'm still very interested in seeing if anyone would be kind enough to assist.

I'm about 90% where I need to be. I have the query grabbing all entries for a single client within a category and then reducing down to only one entry; however, my intent is to return the most recent of all client entries. This is not happening.

SELECT t.title
	 , t.url_title
	 , date_format(t.entry_date, '%d/%m/%Y') AS date
	 , c.cat_url_title
	 , (SELECT field_id_24 FROM exp_weblog_data WHERE entry_id = r.rel_child_id) as Client
  FROM exp_categories AS c 
INNER 
  JOIN exp_category_posts AS cp 
	ON cp.cat_id = c.cat_id 
   AND c.cat_url_title = 'corporate_video' 
INNER 
  JOIN exp_weblog_titles AS t 
    ON t.entry_id = cp.entry_id 
   AND (t.status = 'open' OR t.status = "featured")
INNER
  JOIN (SELECT MAX(rel_id) as max, rel_parent_id
		  FROM exp_relationships 
		 GROUP 
			BY rel_child_id) as ids
	ON t.entry_id = rel_parent_id
LEFT 
  JOIN exp_weblog_data AS d 
	ON t.entry_id = d.entry_id
LEFT 
  JOIN exp_relationships as r
	ON d.entry_id =  r.rel_parent_id
 ORDER 
    BY Client 
ASC

Any tips to achieve what I'm looking to do?

Thanks.