MySQL - What do I need? GROUP BY? Nested SELECT?

[Colman]

Howdy all!

I guess I'm a newbie, because I am stumped (or maybe just too durned
tired). Here's what I got...

CREATE TABLE `nodecat_map` (
`nodecat_id` mediumint(8) unsigned NOT NULL auto_increment,
`nodecat_cat_id ` mediumint(8) unsigned NOT NULL default '0',
`nodecat_node_i d` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`nodecat_id`),
KEY `nodecat_cat_id ` (`nodecat_cat_i d`),
KEY `nodecat_node_i d` (`nodecat_node_ id`)
) TYPE=MyISAM;

CREATE TABLE `nodes` (
`node_id` mediumint(8) unsigned NOT NULL auto_increment,
`node_content_i d` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`node_id`),
KEY `node_content_i d` (`node_content_ id`)
) TYPE=MyISAM;

CREATE TABLE `text` (
`text_id` mediumint(8) unsigned NOT NULL auto_increment,
`text_content_i d` mediumint(8) unsigned NOT NULL default '0',
`text_text` mediumtext NOT NULL default '',
`text_timestamp _dt` int(11) NOT NULL default '0',
PRIMARY KEY (`text_id`),
KEY `text_content_i d` (`text_content_ id`)
) TYPE=MyISAM;

Plus a 'CAT' table not shown, but referred to in NODECAT_MAP by
NODECAT_CAT_ID.

I don't want to spend too much time explaining how/why the tables are
organized like this, but here's a basic rundown of their
relationships:

1) NODECAT_MAP maps NODES to CATS (categories, basically folders),
with many NODES associated with a single CAT.

2) A NODE is basically a document whose content is stored in a TEXT
record, and in order to track multiple revisions of a NODE document
many TEXT records can be associated with a single NODE.



Here's what I CAN do ...

Given a CAT_ID (in this instance "5") I can successfully return all
records with the following query:

SELECT ncat.*, n.*, txt.*
FROM nodecat_map ncat
LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
WHERE ncat.nodecat_ca t_id=5
ORDER BY ncat.nodecat_id DESC, txt.text_timest amp_dt DESC ;



Here's what I WANT to do ...

First, the above query may return 3 NODES with 2 TEXT revisions each
for a total of 6 records. I only want it to return 3 records: the
most recent TEXT rec (based on the field text.text_times tamp_dt) for
each NODE.

I hope this makes sense. Thanks to anyone who can offer some
suggestions!

-Colman

[Sugapablo]

Re: MySQL - What do I need? GROUP BY? Nested SELECT?

In article <4accd45d.04031 30424.1c8ff65d@ posting.google. com>, Colman wrote:
[color=blue]
> SELECT DISTINCT ncat.nodecat_id , n.*, txt.*
> FROM nodecat_map ncat
> LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
> LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
> WHERE ncat.nodecat_ca t_id=5
> ORDER BY ncat.nodecat_id DESC, txt.text_timest amp_dt DESC ;[/color]

Maybe that would work?



--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@12jab ber.com <--jabber IM ]

[Garp]

Re: MySQL - What do I need? GROUP BY? Nested SELECT?


"Colman" <colmanNOSPAMFO RME@rominato.co m> wrote in message
news:4accd45d.0 403130424.1c8ff 65d@posting.goo gle.com...[color=blue]
> Howdy all!
>
> I guess I'm a newbie, because I am stumped (or maybe just too durned
> tired). Here's what I got...
>
> CREATE TABLE `nodecat_map` (
> `nodecat_id` mediumint(8) unsigned NOT NULL auto_increment,
> `nodecat_cat_id ` mediumint(8) unsigned NOT NULL default '0',
> `nodecat_node_i d` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY (`nodecat_id`),
> KEY `nodecat_cat_id ` (`nodecat_cat_i d`),
> KEY `nodecat_node_i d` (`nodecat_node_ id`)
> ) TYPE=MyISAM;
>
> CREATE TABLE `nodes` (
> `node_id` mediumint(8) unsigned NOT NULL auto_increment,
> `node_content_i d` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY (`node_id`),
> KEY `node_content_i d` (`node_content_ id`)
> ) TYPE=MyISAM;
>
> CREATE TABLE `text` (
> `text_id` mediumint(8) unsigned NOT NULL auto_increment,
> `text_content_i d` mediumint(8) unsigned NOT NULL default '0',
> `text_text` mediumtext NOT NULL default '',
> `text_timestamp _dt` int(11) NOT NULL default '0',
> PRIMARY KEY (`text_id`),
> KEY `text_content_i d` (`text_content_ id`)
> ) TYPE=MyISAM;
>
> Plus a 'CAT' table not shown, but referred to in NODECAT_MAP by
> NODECAT_CAT_ID.
>
> I don't want to spend too much time explaining how/why the tables are
> organized like this, but here's a basic rundown of their
> relationships:
>
> 1) NODECAT_MAP maps NODES to CATS (categories, basically folders),
> with many NODES associated with a single CAT.
>
> 2) A NODE is basically a document whose content is stored in a TEXT
> record, and in order to track multiple revisions of a NODE document
> many TEXT records can be associated with a single NODE.
>
>
>
> Here's what I CAN do ...
>
> Given a CAT_ID (in this instance "5") I can successfully return all
> records with the following query:
>
> SELECT ncat.*, n.*, txt.*
> FROM nodecat_map ncat
> LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
> LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
> WHERE ncat.nodecat_ca t_id=5
> ORDER BY ncat.nodecat_id DESC, txt.text_timest amp_dt DESC ;
>
>
>
> Here's what I WANT to do ...
>
> First, the above query may return 3 NODES with 2 TEXT revisions each
> for a total of 6 records. I only want it to return 3 records: the
> most recent TEXT rec (based on the field text.text_times tamp_dt) for
> each NODE.
>
> I hope this makes sense. Thanks to anyone who can offer some
> suggestions!
>
> -Colman[/color]

Wow. I was going to answer this properly before I realised how far off-topic
this is. Instead, I'll just say "LIMIT 0,0" and see how far you get with
that.

Garp

[Colman]

Re: MySQL - What do I need? GROUP BY? Nested SELECT?

Sugapablo <russREMOVE@sug apablo.com> wrote in message news:<slrnc5607 e.2il.russREMOV E@dell.sugapabl o.net>...[color=blue]
> In article <4accd45d.04031 30424.1c8ff65d@ posting.google. com>, Colman wrote:
>[color=green]
> > SELECT DISTINCT ncat.nodecat_id , n.*, txt.*
> > FROM nodecat_map ncat
> > LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
> > LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
> > WHERE ncat.nodecat_ca t_id=5
> > ORDER BY ncat.nodecat_id DESC, txt.text_timest amp_dt DESC ;[/color]
>
> Maybe that would work?[/color]

Thank you for the suggestion. I tried it, but it did not fix my
problem.

Correct me if I am wrong, but your change to the original query was to
add the DISTINCT option. I believe it did not work because the
DISTINCT option simply filters out duplicate rows. The problem isn't
the original query returning duplicate rows, when in fact each row is
"distinct" because each TEXT record is distinct.

I can envision something like adding "LIMIT 1" to just the TEXT
portion of the query, but you can only do that to the entire query,
which does not help. Hence I am thinking a nested Select, but I am
having trouble with that. I don't think MySQL supports it?

-Colman

[Colman]

Re: MySQL - What do I need? GROUP BY? Nested SELECT?

"Garp" <garp7@no7.blue yonder.co.uk> wrote in message news:<5_D4c.156 08$po2.32849823 4@news-text.cableinet. net>...[color=blue]
> Wow. I was going to answer this properly before I realised how far off-topic
> this is. Instead, I'll just say "LIMIT 0,0" and see how far you get with
> that.
>
> Garp[/color]

Off-topic? :) Yes, a little faux pas on my part.

[Colman]

My Solution

I apologize. This is terribly off-topic. However, I have come up
with a solution -- not an ideal solution (single query) -- but it will
do. Please see below...

colmanNOSPAMFOR ME@rominato.com (Colman) wrote in message news:<4accd45d. 0403130424.1c8f f65d@posting.go ogle.com>...[color=blue]
> Howdy all!
>
> I guess I'm a newbie, because I am stumped (or maybe just too durned
> tired). Here's what I got...
>
> CREATE TABLE `nodecat_map` (
> `nodecat_id` mediumint(8) unsigned NOT NULL auto_increment,
> `nodecat_cat_id ` mediumint(8) unsigned NOT NULL default '0',
> `nodecat_node_i d` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY (`nodecat_id`),
> KEY `nodecat_cat_id ` (`nodecat_cat_i d`),
> KEY `nodecat_node_i d` (`nodecat_node_ id`)
> ) TYPE=MyISAM;
>
> CREATE TABLE `nodes` (
> `node_id` mediumint(8) unsigned NOT NULL auto_increment,
> `node_content_i d` mediumint(8) unsigned NOT NULL default '0',
> PRIMARY KEY (`node_id`),
> KEY `node_content_i d` (`node_content_ id`)
> ) TYPE=MyISAM;
>
> CREATE TABLE `text` (
> `text_id` mediumint(8) unsigned NOT NULL auto_increment,
> `text_content_i d` mediumint(8) unsigned NOT NULL default '0',
> `text_text` mediumtext NOT NULL default '',
> `text_timestamp _dt` int(11) NOT NULL default '0',
> PRIMARY KEY (`text_id`),
> KEY `text_content_i d` (`text_content_ id`)
> ) TYPE=MyISAM;
>
> Plus a 'CAT' table not shown, but referred to in NODECAT_MAP by
> NODECAT_CAT_ID.
>
> I don't want to spend too much time explaining how/why the tables are
> organized like this, but here's a basic rundown of their
> relationships:
>
> 1) NODECAT_MAP maps NODES to CATS (categories, basically folders),
> with many NODES associated with a single CAT.
>
> 2) A NODE is basically a document whose content is stored in a TEXT
> record, and in order to track multiple revisions of a NODE document
> many TEXT records can be associated with a single NODE.
>
>
>
> Here's what I CAN do ...
>
> Given a CAT_ID (in this instance "5") I can successfully return all
> records with the following query:
>
> SELECT ncat.*, n.*, txt.*
> FROM nodecat_map ncat
> LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
> LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
> WHERE ncat.nodecat_ca t_id=5
> ORDER BY ncat.nodecat_id DESC, txt.text_timest amp_dt DESC ;
>
>
>
> Here's what I WANT to do ...
>
> First, the above query may return 3 NODES with 2 TEXT revisions each
> for a total of 6 records. I only want it to return 3 records: the
> most recent TEXT rec (based on the field text.text_times tamp_dt) for
> each NODE.
>
> I hope this makes sense. Thanks to anyone who can offer some
> suggestions!
>
> -Colman[/color]

I'm using two queries...

The first uses GROUP BY and the MAX function to yield the IDs of the
most recent TEXT records only (implicit by the TEXT_ID which is auto
generated, hence the higher the ID, the more recent it is - and thus
MAX works fine here).

SELECT max(txt.text_id ) AS MAX_ID
FROM nodecat_map ncat
LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
WHERE ncat.nodecat_ca t_id=5
GROUP BY txt.text_conten t_id ;

This can then be used to create a set of paramters like so...

$in_records = '(1, 2, 3)' ;

Which I can use with the IN function in the query below...

SELECT ncat.*, n.*, txt.*
FROM nodecat_map ncat
LEFT JOIN nodes n ON ncat.nodecat_no de_id=n.node_id
LEFT JOIN text txt ON n.node_content_ id=txt.text_con tent_id
WHERE txt.text_id IN $in_records
ORDER BY ncat.nodecat_id ;

Which then yields what I want.

-Colman