MySQL Syntax Problem

[pootlecat]

Hi,
I am a beginner at complicated MySQL queries and I am hoping someone will be able to tell me if something like this is possible and what the correct syntax might be:

SELECT * WHERE Disabled='0' AND SubLevel='Sub' AND (SELECT * FROM files WHERE Category='Baby/Child' OR Category2='Baby/Child' OR Category3='Baby/Child' OR Category4='Baby/Child') ORDER by ID

Thanks for any help you can give!

[GJay]

SELECT * FROM <table1> t, files f WHERE t.disabled='0' AND f.Category='Baby/Child' OR ...etc...ORDER BY t.id;

YOu need to specify a table to select from, and then if the tables share any column-names (e.g. 'id') you need to specify which you're referring to with the prefix as I have above.

[pootlecat]

Sorry, yes I see I missed that part. I am referring to the same table (files) in each case. Does that make a difference if do I still do it the way you mentioned?

[GJay]

What are you actually trying to do? It's not at all obvious from the query...is the sub-select meant to be to select a different field, or meant to be part of the where-clause for the outer query?

Table structure and what you want to do would help immensely.

[guelphdad]

show some sample rows from your table and then show the sample output that you would get with your query, (some rows would match your search query and others won't for example).

realize that when you are looking at your data and you don't share that as well as the layout of your tables we have nothing to help us figure out what you are doing as GJay says.

[pootlecat]

Hi again :)
Here is some sample data from my table:

ID, Sublevel, Category, Category2, Category3, Category4, Disabled
70783, Sub, Miscellaneous, Animals/Wildlife, Nature, People, 0
70784, Sub, Miscellaneous, Animals/Wildlife, Travel/Destinations, People, 0
70785, Sub, Travel/Destinations, Animals/Wildlife, Miscellaneous, People, 0
70786, Pro, Travel/Destinations, Animals/Wildlife, Miscellaneous, Nature, 0

Say, for example, I want to find all the rows where Disabled='0', Sublevel='Sub' and where 'Miscellaneous' appears in any of the 4 Category columns.
Does that help?

[GJay]

SELECT * FROM table WHERE disabled=0 AND sublevel='sub' AND (category1='miscellaneous' OR category2='miscellaneous' OR category4='miscellaneous' OR category4='miscellaneous');

A better solution, in case you decide you want another category column, or mis-spell 'miscellaneous' when entering a new item, would be to separate out the categories:

your current table would become (i'll call it 'items' for the simplicity later):
items(id, sublevel, disabled)
categories(id,name)
categories_items(category_id,item_id)

And you would have the query as follows:
SELECT i.*, c.name FROM items i JOIN categories_items ci ON (i.id=ci.item_id) JOIN categories c ON (ci.categoryid=c.id) WHERE c.name='miscellaneous' AND i.disabled=0 AND i.sublevel='sub';

[pootlecat]

Thanks so much - I will give that a try :)

Edit - worked like a charm. Thanks again!