Multiple Results

[Anon]

hello all!

i've this 2 tables:
table Stories
| story_id | headline | shortname
+----------+----------+-------------
| 1 | what???? |tecnicachassis
| 2 | xpto |tecnicamotor
| 3 | eieiei |front_page
| 4 | test |10200gaz

and table Sections
+------------+-----------+----------------+
| section_id | parent_id | shortname |
+------------+-----------+----------------+
| 1 | 0 | front_page |
| 49 | 1 | provas |
| 50 | 1 | tecnica |
| 51 | 1 | teste |
| 52 | 49 | 18 |
| 63 | 51 | testechassis |
| 54 | 52 | 18pista |
| 55 | 52 | 18tt |
| 56 | 49 | 110 |
| 57 | 56 | 10200gaz |
| 58 | 56 | 10200elec |
| 59 | 56 | 110ttelec |
| 60 | 56 | 110pistaelec |
| 61 | 1 | trofeus |
| 62 | 56 | 110pista235 |
| 64 | 51 | testemotor |
| 65 | 51 | testelect |
| 66 | 51 | testevarios |
| 67 | 50 | tecnicachassis |
| 68 | 50 | tecnicamotor |
| 69 | 61 | trofeucro |
| 70 | 61 | trofeucma |
| 71 | 61 | trofeucrap |
| 72 | 1 | novidades |
| 73 | 50 | tectransmissao |
| 74 | 50 | tecembraiagem |
| 75 | 50 | tectrucosdicas |
| 76 | 1 | faq |
| 77 | 76 | faqmotor |
| 78 | 76 | facglossario |
| 79 | 1 | fepra |
| 80 | 79 | fepranews |
| 81 | 79 | feprarules |
| 82 | 49 | prova15 |
+------------+-----------+----------------+

i'm gonna try to explain the easy way...

i want a query to select all stories in a section.
if i select section TECNICA (section_id=50/parent_id=1) i want to list ALL stories that belongs to TECNICA section and all stories that belongs to TECNICA child-sections (ex.: Stories.story_id=1 belongs to Sections.shortname='tecnicamotor' that is child of Sections.shortname='tecnica' that is parent of Sections.shortname='tecnicamotor' and so on.... and i want to list ALL news from those child and parent sections)
and i WISH that query in only 1 query (if possible)...

i've worked out this query
SELECT stories.story_id, stories.headline, stories.shortname FROM stories INNER JOIN WHERE stories.shortname='tecnica'

and this doesn't give anything (of course 'cause i search for any stories that belong to shortname 'tecnica' and there is none...)Now i want to list ALL stories from 'tecnica' and stories that belongs to ALL child sections of 'tecnica'....

HOW?!?!?! i even installed MyODBC for use w/ MSACCESS2000 (sucks!) to build a query and i can't get through it...

HHHEEEEEEELLLLLLLLPPPPPPPP!!!!

thanks in advance

[Anon]

Good use of a reflexive relationship in sections. One suggestion, wouldn't it make more sense to have section_id in place of shortname in stories? I believe it would be a cleaner design as that is the key. Try to use the key columns in where clause whenever possible.

SELECT stories.story_id, stories.headline, stories.shortname, sections.shortname
FROM stories
INNER JOIN sections ON (stories.shortname = sections.shortname) INNER JOIN sections s2 ON (sections.section_id = s2.parent_id);

[Anon]

Hi

thanx C.

i'm gonna try that....in response to your reflexion about why not using ID instead of SHORTNAME, well...in the beginning i started the DB Design like that and i'am kinda "slow worker" 😉
but i'm gonna change that also 🙂 thanx

BTW, the database is for a portugues Radio Controled Car online magazine etc...