query working in ofline db but not online db

[fenway]

At no point did you ever give me this simple relationship... even though I asked for it many, many times.  This should work:

 

SELECT content_info. * ,ct. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info AS ci
INNER JOIN content_text AS ct ON ct.infoId =ci.Id
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE ci.Id =1
LIMIT 0 , 30

[anatak]

yes I only realised yesterday that there is an actual relationship with a foreign key in the content_text table but I mentioned that it has a one to many relationship before.

 

I never needed this relationship since the original query used the primary key of content_text.

The primary key of content_info (Id) and the primary key of content_text(Id) are known before the script builds the query.

 

The problem with the query you just wrote is that it will get all the content_text with content_text.InfoId = content_info.Id

there are multiple rows (different languages) of content_text that have the same content_info.Id in the content_text.infoId

 

 

[anatak]

SELECT content_info. * , ct. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info AS ci
INNER JOIN content_text AS ct ON ct.infoId = ci.Id
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE ci.Id =4
LIMIT 0 , 30 

 

MySQL said: Documentation

#1051 - Unknown table 'content_info'

[anatak]

I am sorry to say this fenway but I feel that we are running in circles.

the topic title is also not reflective on what we are trying to do so that is why I wanted to start a new topic so that someone else might take an interest and help us out.

[fenway]

That's because you're not even trying... I changed a table alias, forgot to change the column name.

 

SELECT ci. * , ct. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info AS ci
INNER JOIN content_text AS ct ON ct.infoId = ci.Id
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE ci.Id =4
LIMIT 0 , 30 

 

 

[anatak]

But this query still has the WHERE clause.

That is what is confusing me.

I thought the whole point was to rewrite the WHERE clause into JOINS ?

 

SELECT ci. * , ct. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info AS ci
INNER JOIN content_text AS ct ON ct.infoId = ci.Id
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE ci.Id =4
LIMIT 0 , 30

MySQL said: Documentation

#1054 - Unknown column 'content_info.Cat_m2m_Sub_Cat_Id' in 'on clause'

 

 

I don't know why you use ci as an alias but I changed it into this

SELECT content_info.* , ct.* , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info
INNER JOIN content_text AS ct ON ct.infoId = content_info.Id
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE content_info.Id =4
LIMIT 0 , 30

this works but as I predicted a few posts ago it returns all the content_text information

[fenway]

That's because we're mixing inners and lefts.... and the point is not to exclude where clauses, that would be crazy, but to move join conditions to on clauses.  I'm not at my test box, try the following:

 

SELECT ci. * , ct. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM ( content_info AS ci
INNER JOIN content_text AS ct ON ct.infoId = ci.Id )
LEFT JOIN content_cat_m2m_sub_cat ON content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id
LEFT JOIN content_sub_cat_m2m_element ON content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id
WHERE ci.Id =4
LIMIT 0 , 30