ORDER BY, but mysql says the column doesn't exist

schwim

Hi there folks,

I'm trying to use the following:

	SELECT
		*
	FROM
		favecats
	WHERE
		favecats.user_id = '$viewing_user_id'
	AND
		EXISTS (
			SELECT
				name, NSFW
			FROM
				categories
			WHERE
				categories.id = favecats.cat_id
		)
	ORDER BY categories.name ASC

The categories table has the name I want to sort by while the favecats has the actual cat id's for each user. I've tried both categories.name and simply name, but both result in an "unknown column" error. The column most definitely exists, so I'm not sure what I'm doing wrong.

Any insight would be greatly appreciated!

Derokorian

You aren't selecting the categories table at all in the query with an order by. You'll want to use a join instead of a sub-query.

NogDog

categories.name only exists within the sub-query, not within the context of the main query, where you are trying to do the order by. You'll either need to do a join instead of the exists(sub-query), or I suppose you could add a sub-query to the select portion in order to get the category there, but that would seem inefficient to me.

PS: Darn, I typed that too slowly. 😉

schwim

Thanks very much guys, I'm pretty sure I understand what you're saying; that only the parent query (outside the () ) can be used for order in it's current format.

Thanks to my handy-dandy, always faithful join tutorial, I figured an inner join was the way to go, since it grabs only a record where it exists in both tables, which it must in my case. Any insight on whether I I executed it incorrectly or is it A-OK?

$q_favecats = "SELECT * FROM favecats INNER JOIN categories ON categories.id = favecats.cat_id ORDER BY categories.name ASC";

Thanks again for your help!

Derokorian

I would suggest not using the SELECT * syntax and instead list the data you want to get. This has the added benefit of making the code easier to manage later, as you know exactly what to expect from this query. As far as converting to a join, looks like you did good aside from the fact that you lost your where user_id = $viewing_user_id part of the query.

schwim

Derokorian;11044819 wrote:
I would suggest not using the SELECT * syntax and instead list the data you want to get. This has the added benefit of making the code easier to manage later, as you know exactly what to expect from this query. As far as converting to a join, looks like you did good aside from the fact that you lost your where user_id = $viewing_user_id part of the query.

Oh, crap, I totally missed that, thanks!

As for selecting only what I need, can I ask how I'd do that? If I need name from categories and id from favecats, do I just SELECT id, name and it will know to grab it in which table it exists, or do I need to SELECT categories.name, favecats.id?

Thanks again!

Derokorian

schwim;11044821 wrote:
do I just SELECT id, name and it will know to grab it in which table it exists, or do I need to SELECT categories.name, favecats.id?

You can select without prefixing with the table only if the column names are unique. If both tables have an id column for example, then you would need to prefix that column with the table name, to get the correct id. Note, you can also alias the tables in the FROM clause, thereby saving key strokes. For example:

SELECT
    cat.nam,
    fav.id
FROM
    categories as cat
JOIN
    favecats as fav
    ON fav.cat_id = cat.id
WHERE
    user_id = $viewing_user_id
ORDER BY 
    cat.name

schwim

Thanks very much for that. I really like the alias element. It speaks to my inner obsessive compulsive 🙂