Preserved Topic: Another SQL question...

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?

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

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).]

DL: OK try something like:

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:

$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

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 -

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 =)