query working in ofline db but not online db

anatak · July 17, 2007

Hello,

I have a query that works in my ofline db but not in the online version.

here is the query

SELECT content_info.*, content_text.*, content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId FROM content_info, content_text 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 = 1 AND content_text.Id = 1 ;

ofline db is 4.1.9-max

online db is 5.0.27-standard-log

the error I get in the online version is

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

I am puzzled why I get this error.

Any help is very much appreciated

anatak

fenway · July 17, 2007

It's due to the comma operator that you're using to JOIN tables... either switch this to a proper JOIN (good idea) or wrap parens around (content_info, content_text) to fix it for now in 5.0 (bad idea).

anatak · July 17, 2007

hello fenway,

could you give me the example of the proper join for this query ?

I had no idea that my join syntax was faulty.

thank you

anatak

fenway · July 17, 2007

The comma operator is an implicit full join... and the precedence of this operator was changed (see here and here.

anatak · July 18, 2007

So If I understand correctly it is not allowed to have a WHERE clause when you JOIN tables ?

Or am I missing the point ?

If you can not have a where clause with joins do I have to replace the where with joins ?

I don't see how I can replace

WHERE content_info.Id = 1 AND content_text.Id = 1 ;

with JOIN syntax

WHERE content_info.Id = 1

I guess could be replaced with

JOIN conten_info c_i ON c_i.Id = 1

but after that I don't see how to replace the content_text.Id = 1 with a join

anatak

fenway · July 18, 2007

You have to include a join condition for content_text and context_info.

anatak · July 18, 2007

yes but I have no idea how to write a Join condition with the AND included.

Is there someone who could give me an (abstract) example using three or more tables ?

fenway · July 20, 2007

yes but I have no idea how to write a Join condition with the AND included.

Is there someone who could give me an (abstract) example using three or more tables ?

How are these tables linked?

anatak · July 20, 2007

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info, content_text
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 =1 AND content_text.Id =1 LIMIT 0 , 30

tables

content_info (holds all the information about content except text like date, author, price,..)

content_text (holds all the textual information about content like title, text, translator,...)

content_cat_m2m_sub_cat (holds the id of the content_category and the id of the content_sub_category)

content_sub_cat_m2m_element (holds the id of the content_sub_category and the id of the content_element)

content_info is linked to content_text using content_info.id = content_text.infoId

content_info is linked to content_cat_m2m_sub_cat using content_info.Cat_m2m_Sub_Cat_Id = content_cat_m2m_sub_cat.Id

content_info is linked to content_sub_cat_m2m_element using content_info.Sub_Cat_m2m_Element_Id = content_sub_cat_m2m_element.Id

I hope this makes it clear.

Here are the structures

# Table structure for table `content_info`
#

CREATE TABLE `content_info` (
  `Id` int(11) NOT NULL auto_increment,
  `UpdateDate` datetime default NULL,
  `InsertDate` date default NULL,
  `PublishDate` date default NULL,
  `Author` int(11) default NULL,
  `Cat_m2m_Sub_Cat_Id` int(11) default NULL,
  `Sub_Cat_m2m_Element_Id` int(11) default NULL,
  `Picture` int(11) default NULL,
  `Place` int(11) default NULL,
  `StartDate` datetime default NULL,
  `EndDate` datetime default NULL,
  `Price` int(11) default NULL,
  `Hide` tinyint(1) NOT NULL default '1',
  `Hit` int(11) default NULL,
  `Sponsor` tinyint(1) NOT NULL default '1',
  `SponsorLevel` tinyint(1) default NULL,
  `SponsorView` int(11) NOT NULL default '0',
  `SponsorLogo_1` varchar(255) default NULL,
  `SponsorLogo_2` varchar(255) default NULL,
  `SponsorLogo_3` varchar(255) default NULL,
  `OwnerId` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

#
# Dumping data for table `content_info`
#

INSERT INTO `content_info` (`Id`, `UpdateDate`, `InsertDate`, `PublishDate`, `Author`, `Cat_m2m_Sub_Cat_Id`, `Sub_Cat_m2m_Element_Id`, `Picture`, `Place`, `StartDate`, `EndDate`, `Price`, `Hide`, `Hit`, `Sponsor`, `SponsorLevel`, `SponsorView`, `SponsorLogo_1`, `SponsorLogo_2`, `SponsorLogo_3`, `OwnerId`) VALUES (1, '2007-07-17 21:19:04', '2007-07-17', '2007-07-17', 1, 19, 1, 5, 1, '2007-07-17 00:00:00', '2007-07-17 00:00:00', 0, 1, 2, 1, 0, 0, NULL, NULL, NULL, NULL);
    

# Table structure for table `content_text`
#

CREATE TABLE `content_text` (
  `Id` int(11) NOT NULL auto_increment,
  `LanguageId` int(11) default NULL,
  `TranslatorId` int(11) default NULL,
  `InfoId` int(11) default NULL,
  `Title` varchar(255) default NULL,
  `Subtitle` varchar(255) default NULL,
  `Text` text,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

#
# Dumping data for table `content_text`
#

INSERT INTO `content_text` (`Id`, `LanguageId`, `TranslatorId`, `InfoId`, `Title`, `Subtitle`, `Text`) VALUES (1, 1, 1, 1, 'test', 'test', 'test');
    

# Table structure for table `content_cat_m2m_sub_cat`
#

CREATE TABLE `content_cat_m2m_sub_cat` (
  `Id` int(11) NOT NULL auto_increment,
  `CategoryId` int(11) default NULL,
  `SubCategoryId` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

#
# Dumping data for table `content_cat_m2m_sub_cat`
#

INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (2, 2, 2);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (3, 2, 3);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (4, 2, 4);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (5, 2, 5);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (6, 3, 6);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (7, 3, 7);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (8, 3, ;
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (1, 1, 1);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (19, 2, 1);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (20, 3, 1);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (21, 4, 1);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (22, 5, 1);
INSERT INTO `content_cat_m2m_sub_cat` (`Id`, `CategoryId`, `SubCategoryId`) VALUES (23, 7, 1);
    

# Table structure for table `content_sub_cat_m2m_element`
#

CREATE TABLE `content_sub_cat_m2m_element` (
  `Id` int(11) NOT NULL auto_increment,
  `SubCategoryId` int(11) default NULL,
  `ElementId` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=51 ;

#
# Dumping data for table `content_sub_cat_m2m_element`
#

INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (2, 2, 2);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (3, 2, 3);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (4, 3, 4);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (5, 3, 5);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (6, 3, 6);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (7, 4, 7);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (8, 4, ;
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (9, 4, 9);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (10, 4, 10);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (11, 5, 11);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (12, 5, 12);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (13, 6, 13);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (14, 6, 14);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (15, 6, 15);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (16, 8, 11);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (17, 8, 16);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (1, 1, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (38, 6, 17);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (39, 6, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (40, 13, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (41, 2, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (42, 3, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (43, 4, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (44, 5, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (45, 7, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (46, 8, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (47, 9, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (48, 10, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (49, 11, 1);
INSERT INTO `content_sub_cat_m2m_element` (`Id`, `SubCategoryId`, `ElementId`) VALUES (50, 12, 1);

Thanks for the help

anatak

fenway · July 27, 2007

It does... so your on clause would be :

content_info.id = content_text.infoId

And your where clause would be:

WHERE content_info.Id =1

Presumably, you won't need the rest of your where clause, since it should be redundant... then you can easily switch to all joins.

anatak · August 27, 2007

sorry for the long time between your reply and my reaction.

I was on a long (but not long enough) holiday.

I am sorry but the second part of the WHERE clause content_text.Id =1

Is also needed.

fenway · August 27, 2007

sorry for the long time between your reply and my reaction.

I was on a long (but not long enough) holiday.

I am sorry but the second part of the WHERE clause content_text.Id =1

Is also needed.

You can still have the ON clauses... and what makes you think it's needed.

anatak · August 27, 2007

this query is for selecting the information about an article (date, author, place) and the actual text (title, subtitle, text) of an article.

the text can be written in more than one language.

you want to update an article (in one language) so I have to select only the text out of that language and thus I need the content_text.Id

the fact that in this example the content_text.Id and the conten_info.Id are both 1 is coincidence (sorry forgot to tell you this)

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId

FROM content_info, content_text

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

LEFT JOIN content_info as ci on ci.Id = 1

LEFT JOIN content_text as ct on ct.Id = 1

does not return the single row I was expecting.

Maybe I should just go with the bad solution (using brackets) and live with it.

WHERE content_info.Id =1 AND content_text.Id =1 LIMIT 0 , 30

fenway · August 27, 2007

Thanks for clarifying.... so as I was inquiring earlier, content_info and content_text are NOT linked?

anatak · August 27, 2007

Content_info and content_text have a one to many relationship

one content_info can have more than one content_text

one content_text has one content_info.

I hope that clarifies it.

fenway · August 27, 2007

Sort of.... try this:

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId
FROM content_info
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
LEFT JOIN content_info as ci on ci.Id = 1
LEFT JOIN content_text as ct on ct.Id = 1

anatak · August 27, 2007

result

SQL query: Documentation

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId

FROM content_info

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

LEFT JOIN content_info AS ci ON ci.Id =1

LEFT JOIN content_text AS ct ON ct.Id =1

LIMIT 0 , 30

MySQL said: Documentation

#1051 - Unknown table 'content_text'

I understand why I get the error I just don't know how I can work around it.

LEFT JOIN content_text AS ct ON ct.Id =1

can never work since we are trying to join content_text with content_info on a only content_text key (I hope you understand what I am trying to say)

I tried this

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId

FROM (

content_info, content_text

)

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

LEFT JOIN content_info AS ci ON ci.Id =10

WHERE content_text.Id =79

LIMIT 0 , 30

as a step in the right direction but then I get a selection that does not limit it to ci.Id = 10 but I get all the content_info records.

Back to bed for a few hours

thx for all the help till now Fenway

fenway · August 28, 2007

Well, you can inner join to actually limit the ci record... as for the error, I don't really understand what you're saying.

anatak · August 28, 2007

Inner join does not work.

This is the error

#1051 - Unknown table 'content_text'

content_text is not known

Is there someone else who can maybe shed a light on this problem ?

fenway · August 28, 2007

That's because you changed the alias to ct --- look at the select column list.

anatak · August 29, 2007

SELECT content_info. * , content_text. * , content_cat_m2m_sub_cat.CategoryId, content_cat_m2m_sub_cat.SubCategoryId, content_sub_cat_m2m_element.ElementId

FROM content_info, content_text

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

LEFT JOIN content_info AS ci ON ci.Id =5

LEFT JOIN content_text AS ct01 ON ct01.InfoId =5

INNER JOIN content_text AS ct02 ON ct02.Id =75

LIMIT 0 , 30

MySQL said: Documentation

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

still not working

fenway · August 29, 2007

I don't know what you're doing.... the list of columns you're asking for still have the same issue... and now you've reverted to the old query.

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
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
LEFT JOIN content_info AS ci ON ci.Id =1
LEFT JOIN content_text AS ct ON ct.Id =1
LIMIT 0 , 30

anatak · August 29, 2007

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
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
LEFT JOIN content_info AS ci ON ci.Id =1
LEFT JOIN content_text AS ct ON ct.Id =1
LIMIT 0 , 30

This query selects all the content_info rows and not only the one with ci.Id = 1

still confused about how you can rewrite a

WHERE clause with JOIN

This query selects ALL the content_info rows and not only the one with the ci.Id = 1

fenway · August 30, 2007

I don't see where content_text comes in here... how is it linked to any of these tables?

Either way, this will fix part of the problem:

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
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
****LEFT JOIN content_text AS ct ON ct.Id =1****
LIMIT 0 , 30

anatak · August 30, 2007

content_text is linked to content_info on

content_text.infoId = content_info.Id

but now you have a where clause again in the query.

I thought the whole point was to not use where clauses ?

and this results in this error

Sign In

query working in ofline db but not online db

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information