Get records by category and list the other categories they belong to 1

[stinkybee]

I am trying to return some information that needs to consist of the name from one table and all the categories that each name belongs to from another.

However, I am searching using the category name. In other words I need to list all records that are under a specific category and also list the other categories they belong to.

I would like to be able to achieve this using one lot of sql code but am having difficulties. Here is what I have so far

This gets all records based on the sub category name

SELECT programmes.programmes_name
FROM ((programmes INNER JOIN categories_lookup ON programmes.programmes_id = categories_lookup.programmes_id) 
INNER JOIN sub_categories ON categories_lookup.sub_categories_id = sub_categories.sub_categories_id) 
WHERE (sub_categories.sub_categories_name = sub category name )

and this gets all of the category names

SELECT sub_categories_name FROM (sub_categories INNER JOIN categories_lookup ON sub_categories.sub_categories_id = categories_lookup.sub_categories_id) WHERE categories_lookup.programmes_id = the id of each record from above

As you can see the second sql code needs the id of each record that is returned with the first lot of code.

How can I join these two pieces of sql together so that it returns the required records each of which contains all of the categories that they belong to?



Web Development Manager

http://www.freemoneyoff.co.uk

http://www.freemoneysurveys.co.uk

http://www.free-money-resource.com

 

[BigRed1212]

I need to list all records that are under a specific category and also list the other categories they belong to."

That part is a little confusing and I think a little important.

Could you maybe post some data as it is structured and the desired result?

This all sounds hierarchical and that sort of data can be harder to deal with.

 

[stinkybee]

Ok, I'll try to clarify.

I have the following tables with relevant fields:

progammes - programmes_name

categories_lookup - sub_categories_id, programmesid

sub_categories - sub_categories_name


So if I want to list all programmes with a sub category name of "surveys" for example, I also need the results to grab all other sub_categories_name entries that belong to that programme.

Here is some sample data and expected results:
[pre]
progammes
programmes_id programmes_name
1 name1
2 name2

categories_lookup
sub_categories_id programmesid
1 1
2 1
1 2

sub_categories
sub_categories_id sub_categories_name
1 surveys
2 quizzes

Required results based on a search or list of all programmes with a sub category of "surveys":

programme_name sub_categories_name
name1 surveys, quizzes
name2 surveys
[/pre]

In this example "name1" is under sub categories "surveys" and "quizzes" and programme "name2" is under sub category "surveys". When I list all programmes under a sub category ("surveys" in this example), I also need to show the other sub categories that each programme is associated with.

I hope this makes more sense now.





Web Development Manager

http://www.freemoneyoff.co.uk

http://www.freemoneysurveys.co.uk

http://www.free-money-resource.com

 

[PHV]

Something like this ?

SELECT P.programmes_name, S.sub_categories_name
FROM (programmes P
INNER JOIN categories_lookup C ON P.programmes_id = C.programmes_id)
INNER JOIN sub_categories S ON C.sub_categories_id = S.sub_categories_id
WHERE P.programmes_id IN (SELECT CL.programmes_id
FROM categories_lookup CL INNER JOIN sub_categories SC ON CL.sub_categories_id = SC.sub_categories_id
WHERE SC.sub_categories_name = '[i]sub category name[/i]')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[stinkybee]

That's got it. Thanks

Web Development Manager

http://www.freemoneyoff.co.uk

http://www.freemoneysurveys.co.uk

http://www.free-money-resource.com