need help with mysql query

Question

1st piece of background inf : This is about cms which holds object - object relations and their sorting in single table, columns are object_id, parent_id and sorting order

2nd piece have query with several joins, which i want to sort by 2 parameters. One of those is sorting of the object itself and 2nd is the sorting order of its parent.

The query i have for now is:

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order from object_object WHERE object_id = (SELECT parent_id from object_object WHERE object_id = obj_asset.object_id )) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

And it does not work. This works fine though:

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order from object_object WHERE object_id = (SELECT parent_id from object_object WHERE object_id = 11111 )) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

The error i get is:

1054 - Unknown column 'obj_asset.object_id' in 'where clause'

How can i get it working?

Thanks!

EDIT: i could get around the problem, if i could come up with alternate way to include parents sorting into the query. IS there such a way?

Are you sure that you have object_id column in obj_asset table? — KomarSerjio
– KomarSerjio, Commented Feb 7, 2011 at 21:12
The error message is a pretty good clue in this instance. (You may have to use the tablename.fieldname naming style if the interpreter is attempting to use the wrong object_id field.) :-) — John Parker
– John Parker, Commented Feb 7, 2011 at 21:14
@KomarSerjio - yes i am - as you can see, its not the only place i have used same column&table and it works if i change it for actual id. @middaparka - I understand why the error comes up - in the context of the inner select, there is no obj_asset.object_id as it has not been selected yet... But how can i fix this with just one query? — Odif Yltsaeb
– Odif Yltsaeb, Commented Feb 7, 2011 at 21:18

marapet · Accepted Answer · 2011-02-07 21:47:23Z

1

As you already figured out, the problem is that you are trying to use a column from an outer query in a constraint of a subquery:

  (SELECT sort_order
      from object_object
      WHERE object_id = (SELECT parent_id
                         from object_object
                         WHERE object_id = obj_asset.object_id )
     )

This subquery can be rewritten, but it is not clear how when only looking at your original query.

There is probably an error in your original query (reformatted):

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order
FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order
      from object_object
      WHERE object_id = (SELECT parent_id
                         from object_object
                         WHERE object_id = obj_asset.object_id )
     ) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

The sub query named op is not used in any join or where clause.

My best guess is that you wanted to to the following:

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order
FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order, o2.object_id
    from object_object as o1
    INNER JOIN object_object as o2 ON o1.object_id = o2.parent_id
    ) AS op ON obj_asset.object_id = op.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

answered Feb 7, 2011 at 21:47

marapet

56.9k13 gold badges176 silver badges186 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Odif Yltsaeb Over a year ago

man, i could kiss you right now :P. Thats exactly what i needed. Only one mistake - was supposed to be (SELECT o1.sort_order.... Thanks!

marapet Over a year ago

I'm glad it helped - no kissing please, upvoting is enough :-)

SVGreg · Accepted Answer · 2011-02-07 21:20:22Z

0

I think you need to type this (include obj_asset to FROM):

(SELECT parent_id FROM object_object, obj_asset  WHERE object_object.object_id = obj_asset.object_id )

but not this:

(SELECT parent_id from object_object WHERE object_id = obj_asset.object_id )

answered Feb 7, 2011 at 21:20

SVGreg

2,4081 gold badge14 silver badges17 bronze badges

1 Comment

Odif Yltsaeb Over a year ago

nope. that creates whole new bunch of problems as there is no parent_id in obj_asset and it will also return more than 1 row.

Nathan · Accepted Answer · 2011-02-07 21:44:26Z

0

Your subquery doesn't include obj_asset in the FROM clause.

(SELECT parent_id FROM object_object WHERE object_id = obj_asset.object_id )

Get that part running, and you should have better luck with the whole thing.

Just a hint... if you put some more newlines and indentations in your SQL statements it will be easier for you to spot problems.

edited Feb 7, 2011 at 21:44

answered Feb 7, 2011 at 21:16

Nathan

3,9361 gold badge28 silver badges31 bronze badges

3 Comments

Odif Yltsaeb Over a year ago

meh? yeah.. i know thats where the problem is. I just cant think of any way to fix it atm.

Nathan Over a year ago

oops, I meant FROM clause. Try SELECT parent_id FROM object_object, obj_asset WHERE object_object.object_id = obj_asset.object_id

Odif Yltsaeb Over a year ago

Like i answered to SVGreg - that would rise new problems, as parent_id is not column of obj_asset and if it were, then select would return multiple rows, where i only need one.

Collectives™ on Stack Overflow

need help with mysql query

1054 - Unknown column 'obj_asset.object_id' in 'where clause'

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1054 - Unknown column 'obj_asset.object_id' in 'where clause'

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related