complex query problem

[pouncer]

I have 2 tables group_categories and categories

 

group_categories

lion

tiger

 

categories contains the following animals:

cat

dog

lion

tiger

zebra

 

basically i want to echo all the animals in 'categories' that are NOT in 'group_categories'

so it would be cat,dog, zebra

 

anyone can help me?

[anthylon]

I think you should make some changes to your database.

 

Let's try this:

 

Make three tables in your database:

1. tbl_animals

id animal_name category_id

 

2. tbl_categories

id category_name

 

Than you could provide some combo for users to choose category for animals. When that happend save ID of that category to table tbl_animals.category_id.

 

So later when you want to populate some combo with all animals who are without catergory_id (that means you hadn't set category for those animals) you can make query:

SELECT * FROM tbl_animals WHERE category_id = NULL

.

 

If you want query database to get results of all animals and their categories you can use JOIN method:

SELECT tbl_animals.*, tbl_categories.category_name FROM tbl_animals LEFT JOIN tbl_categories ON tbl_animals.category_id = tbl_categories.id

 

I'm not sure if that could be solution for you. It independents of what you want to do exactly.

 

But I hope this was helpfull for you.

 

Cheers

 

[pouncer]

i cant see the code in your JOIN thing

 

also, what if i then wanted to remove an animal from a category? to i just put "" in the category id

[Vikas Jayna]

Write a simple left join query and use  "<columname> is null" in the where clause to get the records that are not there in the table on the right.

 

http://dev.mysql.com/doc/refman/5.0/en/join.html