Closed Thread Icon

Preserved Topic: Another SQL question... (Page 1 of 1) Pages that link to <a href="" title="Pages that link to Preserved Topic: Another SQL question... (Page 1 of 1)" rel="nofollow" >Preserved Topic: Another SQL question... <span class="small">(Page 1 of 1)</span>\

Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-08-2003 02:14

Ok, I'm going nuts here...and I'm sure it's because I'm being thick...

This is my table setup (the relevant tables for the query I want to do..)

Very simple -

cat (cat_id, cat_name) //the id# and name of the categories
sub_cat (sub_cat_id, sub_cat name) //the id# and name of the SUB categories
parent (sub_cat_id, cat_id) //the relationships - any one sub-category can belong to any number of categories...

Now, I have no problem pulling most things I've needed out of this, but for some reason can't seem to get what I want this time aorund.

So, what I want:

I want to output a list of each sub-category, and each category that it belongs to, grouped and ordered by sub-category (sub_cat_id and sub_cat_name respectively).

So I would have something like this as the output -

subcategory1 - categoryA
subcategory1 - categoryF
subcategory1 - categoryX

subcategory2 - categoryB
subcategory2 - categoryC

subcategory3 - categoryA

etc, etc...

I have tried a variety of methods, including one big query from all tables, and several queries to each table and soforth, and seem to be brainfarting.

Any tips?

Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 02-08-2003 02:43

basically it should be

select cat.cat_name sub.sub_cat_name parent.sub_id, parent.cat_id from sub, cat, parent

Where sub.sub_cat_id = parent.sub_cat_id
AND cat.cat_id = parent.cat_id

Something like that
I don't have a linking table to test on right now.

.:[ Never resist a perfect moment ]:.

Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-08-2003 03:26

That was one of the first things I tried, and it hasn't worked. It turns up some odd associations, and I havnet' quite figured out how it's making those...incorrect...associations. Appreciate the feedback least shows that I wasn't so far off in my original thinking =)

I have some more ideas to play with though...will report back if I have any luck.

Any other tips in the mean-time still appreciated.

{{edit - no luck whatsoever...I'm at a complete loss =( }}

[This message has been edited by DL-44 (edited 02-08-2003).]

Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 02-08-2003 03:48

DL: OK try something like:

FROM cat, parent, sub_cat
WHERE parent.cat_id = cat.cat_id
GROUP BY parent.sub_cat_id

and then when you are going through the results:

$current_sub_cat = $row[sub_cat_id];
$current_sub_cat_name = $row[sub_cat_name];

if ($previous_sub_cat != $current_sub_cat) {
$output .= "<h3>$current_sub_cat_name</h3>";

$output .= "Other category stuff";

$previous_sub_cat = $current_sub_cat;

[edit: swap the GROUP BY for an ORDER BY and add another WHERE in to join the 3 tables up and it got there (with a few other tweaks]


FAQs: Emperor

Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-10-2003 01:08

Ok, so it seems that what was tripping me up was 2 things - not being specific enough in my WHERE statement, and not properly setting my ORDER BY.

this is the code I ended up using -

cat, parent, sub_cat
parent.cat_id = cat.cat_id
parent.sub_cat_id = sub_cat.sub_cat_id
order by

Thanks again for the replies =)

« BackwardsOnwards »

Show Forum Drop Down Menu