CODEWITHSUNDEEP
mysql
jzon
jzon

Reputation: 95

Issue with retrieving data from Mysql query

The following is my query where I need to get the parentid and id aliases of specific items categories. However, I currently can only get the id instead of the alias.

The issue which I have is how to get both parentid alias and id alias with the results. For example, if an item has an id of 2099 and a parent id of 5, I want to get the alias of the matching id and the parent id alias of that matching id.

Here is my code:

SELECT a.string, b.id, b.alias ,c.title, c.alias as calias, c.parent_id as pid
        FROM tbl_fields a, tbl__content b ,tbl_categories c
        WHERE a.fields_id='75' AND a.value_string = 'steel' AND a.content_id = b.id AND b.categories_id = c.id

My table structure is:

enter image description here

The results which I want are:

City hotel - hotel - appartment
Luxury Hotel - hotel - apartment

Upvotes: 1

Views: 43

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

As it is just one hierarchy level up you want to see (i.e. no recursion), all you have to do is join the categories table twice:

select content.item, cat_parent.alias, cat_self.alias
from content
join categories cat_self on cat_self.id = content.categories_id
join categories cat_parent on cat_parent.id = cat_self.parent_id
where content.id in 
(
  select content_id 
  from fields 
  where fields_id = 75
);

Upvotes: 1

Sachin I
Sachin I

Reputation: 1508

Rename the string column name. You can not use data type as your column name.

Refer Naming scheme of database table fields

Upvotes: 0

Hassaan
Hassaan

Reputation: 7672

Add space between "From" and "fields a".

Use

FROM fields a

instead of

FROMfields a

Upvotes: 0

Related Questions