Closed Thread Icon

Topic awaiting preservation: Another SQL question... (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=12608" title="Pages that link to Topic awaiting preservation: Another SQL question... (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: Another SQL question... <span class="small">(Page 1 of 1)</span>\

 
DL-44
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?

bitdamaged
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 ]:.

DL-44
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 though...at 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).]

Emperor
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:

code:
SELECT *
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:

code:
$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]

___________________
Emps

FAQs: Emperor

DL-44
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 -

code:
select 
*
from
cat, parent, sub_cat
where
parent.cat_id = cat.cat_id
and
parent.sub_cat_id = sub_cat.sub_cat_id
order by
sub_cat.sub_cat_name";



Thanks again for the replies =)



« BackwardsOnwards »

Show Forum Drop Down Menu