[RESOLVED] Reducing queries (probably using JOIN)

[vaaaska]

I have two queries pulling information to create a page but I'd really like to get this down to one query - I think it's possible but I'm not sure (meaning I have been unsuccessful so far):

// query one
$rs = $db->fetchRecord(
"SELECT * 
FROM t_$page[p_table],t_templates 
WHERE id='$page[p_page_id]' 
AND t_$page[p_table].templid=t_templates.templid 
");

// templ_plug comes in the form of 2,5,8,34,21
$plugins = explode(',',$rs['templ_plug']);

// query two
$plug = $db->fetchArray("
SELECT incl_code FROM t_incl 
WHERE (incl_type = '1' AND (incl_id IN (" .implode(',',$plugins). "))) 
OR ((incl_type='1') AND (incl_sys = '1')) 
ORDER BY incl_sys ASC");

// 

These, as they are, work great. Now, taking this further...I'm confused about which kind of JOIN could pull this off. Additionally, because I do need to the sort the order of the second query I wonder if this throws everything into a mess. This is where I am sort of but it's not pulling up anything even close to what it should...

$rs = $db->fetchRecord(
"SELECT incl_code 
FROM t_$page[p_table],t_templates
INNER JOIN t_incl 
WHERE id='$page[p_page_id]' 
AND t_$page[p_table].templid=t_templates.templid 
AND ((incl_type = '1' AND (incl_id IN (templ_plug))) 
OR ((incl_type='1') AND (incl_sys = '1'))) 
ORDER BY incl_sys ASC
");

Helllllp. 😉

[vaaaska]

A littel movement here...but not all the way yet...see the notes below, but it does return all the records I need for information and templates...but not for t_incl...

$rs = $db->fetchRecord(
"SELECT * 
FROM t_$page[p_table],t_templates

// templ_plug contains 49,44 but it only return record 49 - ??? 
LEFT OUTER JOIN t_incl AS i1 ON (i1.incl_id IN (templ_plug)) 

// this line kills everything, but it's needed to get a few more thing...
LEFT OUTER JOIN ((incl_type='1') AND (incl_sys = '1'))

WHERE id='$page[p_page_id]' 
AND t_$page[p_table].templid=t_templates.templid"
);

[vaaaska]

Ok...I'm pretty sure that what I'm tryihng to do is not possible. My JOINS needs to return an array of values and this seems to seriously be throwing things. Back to the old skool.

I'll mark this resolved and leave it for posterity...and perhaps somebody will come along and show me an amazing way to really pull this off.

😉