Small Join Issue

Nixon

Ok, this is my current schema:

wiki_templateDefinitions

wiki_pageContent

This query:

SELECT 
   wpc.content,
   wtd.heading
FROM 
   wiki_templateDefinitions wtd
LEFT OUTER JOIN
   wiki_pageContents wpc
ON
   wpc.wikiTemplateID = wtd.wikiTemplateID
WHERE
   wpc.wikiPageID = '2'
AND
   wtd.wikiTemplateID = '1'
ORDER BY
   headingOrder
ASC

Returns:

content 	                                heading
---------------------------------------------------------
I am linked with heading 2 and wiki page 2	Heading 1
I am linked with heading 2 and wiki page 2	Heading 2

So, it so SO VERY NEARLY there BUT, there is no matching row for wiki page 2 heading 1, so that should be null and not a repeat of the corresponding heading 1 content column. Any help would be appreciated!

May I add that is currently for a free open source project so I'm not trying to contract help for profit.

johanafm

Nixon;10990016 wrote:
So, it so SO VERY NEARLY there BUT, there is no matching row for wiki page 2 heading 1,

I'd claim that there is indeed a matching row

Nixon;10990016 wrote:
so that should be null and not a repeat of the corresponding heading 1 content column.

And that this row is returned.

What data do you have in wiki_templateDefinitions for wtd.wikiTemplateID = '1' and what data do you have in wiki_pageContent for wpc.wikiPageID = '2'?

Also, why are your id columns varchar?

Or, if they aren't, why are you comparing them to string literals? This requires type casting and will not make use of indexing.

Nixon

Thanks for the quick reply, they're not varchars - it's just my bad habits brought on by other languages. A dump of my current table is below:

-- --------------------------------------------------------

--
-- Table structure for table `wiki_pageContents`
--

CREATE TABLE IF NOT EXISTS `wiki_pageContents` (
  `wikiTemplateDefinitionID` int(11) NOT NULL DEFAULT '0',
  `wikiPageID` int(11) NOT NULL DEFAULT '0',
  `wikiTemplateID` int(11) NOT NULL DEFAULT '0',
  `content` text,
  PRIMARY KEY (`wikiPageID`,`wikiTemplateID`,`wikiTemplateDefinitionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `wiki_pageContents`
--

INSERT INTO `wiki_pageContents` (`wikiTemplateDefinitionID`, `wikiPageID`, `wikiTemplateID`, `content`) VALUES
(1, 1, 1, 'I am linked to heading 1 and wiki page 1'),
(2, 1, 1, 'I am linked to heading 2 and wiki page 1'),
(2, 2, 1, 'I am linked with heading 2 and wiki page 2');

-- --------------------------------------------------------

--
-- Table structure for table `wiki_pages`
--

CREATE TABLE IF NOT EXISTS `wiki_pages` (
  `wikiPageID` int(11) NOT NULL AUTO_INCREMENT,
  `wikiTemplateID` int(11) DEFAULT NULL,
  PRIMARY KEY (`wikiPageID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `wiki_pages`
--

INSERT INTO `wiki_pages` (`wikiPageID`, `wikiTemplateID`) VALUES
(1, 1),
(2, 1);

-- --------------------------------------------------------

--
-- Table structure for table `wiki_templateDefinitions`
--

CREATE TABLE IF NOT EXISTS `wiki_templateDefinitions` (
  `wikiTemplateDefinitionID` int(11) NOT NULL AUTO_INCREMENT,
  `wikiTemplateID` int(11) DEFAULT NULL,
  `headingOrder` int(11) DEFAULT NULL,
  `heading` text,
  `description` text,
  `dataType` text,
  PRIMARY KEY (`wikiTemplateDefinitionID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `wiki_templateDefinitions`
--

INSERT INTO `wiki_templateDefinitions` (`wikiTemplateDefinitionID`, `wikiTemplateID`, `headingOrder`, `heading`, `description`, `dataType`) VALUES
(1, 1, 0, 'Heading 1', 'A heading', 'text'),
(2, 1, 1, 'Heading 2', 'Another heading', 'textArea');

-- --------------------------------------------------------

--
-- Table structure for table `wiki_templates`
--

CREATE TABLE IF NOT EXISTS `wiki_templates` (
  `wikiTemplateID` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  `description` text,
  PRIMARY KEY (`wikiTemplateID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `wiki_templates`
--

INSERT INTO `wiki_templates` (`wikiTemplateID`, `name`, `description`) VALUES
(1, 'Generic Template', 'A generic temp-late to use as a bare wiki page');

johanafm

table def

CREATE TABLE IF NOT EXISTS `wiki_pageContents` (
  ,
  ,
  `wikiTemplateID` int(11) NOT NULL DEFAULT '0',
  ,
)

data

INSERT INTO `wiki_pageContents` (``, ``, `wikiTemplateID`, ``) VALUES
(, , 1, ''),
(, , 1, ''),
(, , 1, '');

join condition

ON wpc.wikiTemplateID = wtd.wikiTemplateID

Nixon

johanafm, I'm sorry I don't understand. Are you saying that this is how the table should be set out?

If so then I already have wikiTemplateID in wiki_pageContents, and also the values in wiki_pageContents, then my join also is ON wpc.wikiTemplateID = wtd.wikiTemplateID.

Lars_Berg

select wpc.content,
       wtd.heading
  from wiki_templateDefinitions wtd
  left
  join wiki_pageContents wpc
    on wpc.wikiTemplateID = wtd.wikiTemplateID
   and wpc.wikiPageID = 2
 where wtd.wikiTemplateID = 1
 order 
    by headingOrder asc

Nixon

Hi Lars,

Thank you for your input however that gives me the same issue!

select wpc.content,
       wtd.heading
  from wiki_templateDefinitions wtd
  left
  join wiki_pageContents wpc
    on wpc.wikiTemplateID = wtd.wikiTemplateID
   and wpc.wikiPageID = 2
 where wtd.wikiTemplateID = 1
 order 
    by headingOrder asc

results in:

content 	heading
I am linked with heading 2 and wiki page 2	Heading 1
I am linked with heading 2 and wiki page 2	Heading 2

I keep thinking I have a brainwave and try it but I always come back to square one 🙁 I don't know if it's my architecture thats wrong or I'm trying to do something daft.

I suppose it's a fairly complex idea, what I am essentially trying to do is build dynamic editable objects of any size in mysql which can be used as templates for wiki pages, however I don't want to store rows of redundant data if there is no data under the specific heading. I'm trying to match headings to data based on the template and return NULL content if the heading content is empty, but I still need to print the heading...

Nixon

Ah scrap that, trying to explain it made me have a brainwave and how you used logic in the ON statement also stirred an idea to which I've come up with:

SELECT wpc.content, wtd.heading
FROM wiki_templateDefinitions wtd
LEFT
JOIN wiki_pageContents wpc ON wpc.wikiTemplateID = wtd.wikiTemplateID
AND wpc.wikiTemplateDefinitionID = wtd.wikiTemplateDefinitionID
AND wpc.wikiPageID =2
WHERE wtd.wikiTemplateID =1
ORDER
BY headingOrder ASC

which results in:

content 	heading
NULL	Heading 1
I am linked with heading 2 and wiki page 2	Heading 2

SUCCESS! 🙂

johanafm

The point I was trying to make was that the data you posted initially was correct indeed, and I tried doing so by removing any non-related information from the stuff you posted yourself.

With a little commentary

INSERT INTO `wiki_pageContents` (``, ``, `wikiTemplateID`, ``) VALUES
(, , 1, ''),
(, , 1, ''),
(, , 1, '');

As can be seen here, you insert the value 1 into the field "wikiTeampteID" for all of your records.

ON wpc.wikiTemplateID = wtd.wikiTemplateID

which means that using this field in the ON clause of the join will obviously join all those records against the exact same record in the other table.