MySQL Join Question

jack.mitchell · 26 Oct 2011 at 21:37

Ok, joins are my pet hate. I cannot get my head round them no matter how much I read. I keep trying and one day I know they will click, but today isn't that day. So I need some help with this query.

What I have:

Code:

 $query = "SELECT 
                  wiki_pageContents.content, 
                  wiki_pageTypeDefinitions.heading 
                FROM 
                  wiki_pageTypeDefinitions, 
                  wiki_pageContents  
                WHERE 
                  wikiPageTypeID = '".$data['wikiPageTypeID']."' AND 
                  wiki_pageContents.wikiPageTypeDefinitionID = wiki_pageTypeDefinitions.wikiPageTypeDefinitionID 
                ORDER BY 
                  headingOrder ASC";

What I think is my line of issue:

Code:

                  wiki_pageContents.wikiPageTypeDefinitionID = wiki_pageTypeDefinitions.wikiPageTypeDefinitionID

wiki_pageTypeDefinitions will always have a definition of a wiki page which includes 'header' however it may not always have it's corresponding value from wiki_pageContent.content.

So, what I need is a join of some sort which will always return the header, and if it doesn't find the corresponding content then just return nothing. At the moment it works perfectly as long as the header has its corresponding content, otherwise it doesn't return the row at all, obviously because it evaluate the "==" to true.

If you need any more info or further explanation i can try explaining again. Thanks for reading I really am at my wits end with this!!!!

fez · 26 Oct 2011 at 22:48

SELECT wpc.content, wpt.heading from wiki_pageContents wpc LEFT join wiki_pageTypeDefinitions wpt on wpc.wikiPageTypeDefinitionID = wpt.wikiPageTypeDefinitionID WHERE wikiPageTypeID = '".$data['wikiPageTypeID']."' ORDER BY headingOrder ASC

Might work...

This might help if you havn't seen it yet. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

jack.mitchell · 27 Oct 2011 at 11:28

jack.mitchell · 27 Oct 2011 at 14:26

Code:

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:

Code:

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, any final ideas?

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

topdog@OC · 29 Oct 2011 at 15:15

I don't entirely get what you were trying to achieve with the new changes to the schema (or exactly how they're modelled, seems you have a redundant term linking from pageContent into pageTemplateDefinitions unless there's also a pageTemplate(s) table missing from the list).

However if I go back a post, this is what I see your schema set up to do:

And I think you'd have been better served with one like this:

You'd want records in wiki_PageTypes for each definition (heading) you wish to include, rather than just the single record you had before, so it would be like:

wiki_pageTypes records:
wikiPageTypeID = 1, wikiPageID = 1, wikiPageTypeDefinitionID = 1
wikiPageTypeID = 2, wikiPageID = 1, wikiPageTypeDefinitionID = 2
wikiPageTypeID = 3, wikiPageID = 2, wikiPageTypeDefinitionID = 1
wikiPageTypeID = 4, wikiPageID = 2, wikiPageTypeDefinitionID = 2

Then an outer join to pageContents can be formed to this table for each piece of content you wish to have (e.g. currently 1 for each heading type for page 1, but no content currently for page 2):

wiki_pageContents records:
wikiPageContentID = 1, wikiPageTypeID = 1, content = 'content for page 1, heading 1'
wikiPageContentID = 2, wikiPageTypeID = 2, content = 'content for page 2, heading 2'

Resulting query:

select pc.content, ptd.heading
from wiki_pages p
join wiki_pagetypes pt
on p.wikipagetypeid = pt.wikipagetypeid
join wiki_pagetypedefinitions ptd
on pt.wikipagetypedefinitionid = ptd.wikipagetypedefinitionid
left join wiki_pagecontents pc
on pt.wikipagetypeid = pc.wikipagetypeid
where p.wikipageid = {your filter for 1 or 2 here}
order by ptd.headingorder asc;

HungryHippos · 29 Oct 2011 at 17:48

Does this picture help you understand the joins a little better?

Inner join returns all records where you have matches, i.e. if you join two tables on an ID field you will get back all records where the id numbers exist in both tables.

Left and Right join are the same thing if you reverse the order of the tables, I tend to just Left join. Left join will return all the records from the left table and any it can match to from the right table in the join. Any records not matched will be returned with NULL values for the columns.

jack.mitchell · 6 Nov 2011 at 20:35

Sorry for not updaing on this guys, but I finally figured it out, this was the query I ended up with:

Code:

SELECT 
                  wpc.content,
                  wtd.heading,
                  wtd.wikiTemplateDefinitionID
                FROM 
                  wiki_templateDefinitions wtd
                LEFT JOIN
                  wiki_pageContents wpc
                ON
                  wpc.wikiTemplateDefinitionID = wtd.wikiTemplateDefinitionID
                AND
                  wpc.wikiTemplateID = wtd.wikiTemplateID
                AND
                  wpc.wikiPageID = ".$id."
                WHERE
                  wtd.wikiTemplateID = ".$this->templateID."
                AND
                  (wpc.isCurrent = 1
                  OR
                  wpc.isCurrent IS NULL)
                ORDER BY
                  headingOrder
                ASC

Is there a tool show map out mysql schemas in a visual format, as it takes ages to drawn them out.

RobH · 6 Nov 2011 at 22:55

Everyone has their own rules/opinion on code styling but I find it easier to read in the layout below. It can also help you spot errors, I've come across some SQL from previous developers where I work which are pretty much all on a single line, it's fine if you're the only one reading it but if others need to understand it in the future it just makes things harder.

Code:

SELECT
  wpc.content,
  wtd.heading,
  wtd.wikiTemplateDefinitionID
    
FROM wiki_templateDefinitions wtd
  LEFT JOIN wiki_pageContents wpc ON (
    wpc.wikiTemplateDefinitionID = wtd.wikiTemplateDefinitionID
    AND
    wpc.wikiTemplateID = wtd.wikiTemplateID
    AND
    wpc.wikiPageID = ".$id."
  )

WHERE
  wtd.wikiTemplateID = ".$this->templateID."
  AND (
    wpc.isCurrent = 1
    OR
    wpc.isCurrent IS NULL
  )

ORDER BY headingOrder ASC

You seem to have a reference to the wpc table in your where clause yet you have left joined to it. Adding the condition on the wpc table negates the use of the left join which is why you have added the OR to allow null rows. You just need to move the condition into the join, you don't need to check for the nulls then.

You also seem to be using two keys (wikiTemplateDefinitionID and wikiTemplateID) to join two tables together which seems to suggest that your design may not be quite right.

In terms of tools for viewing the schema, there are various ones, just google for "MySQL entity relationship diagram". This is often known as "reverse engineering" a database schema. You will need foreign keys configured properly on your tables for it to be able to determine the relationships though for a lot of the tools.

If you're still stuck, feel free to reply.

cats_five · 8 Nov 2011 at 16:02

Some interesting ERDs above. To me, just looking at the entity (table) names:

page_type_defs -< pages <- page contents

In words:
page_type_defs defines pages types.
Each page is of one type, but there can be many pages for each type. Since this is an 1:n relationship there is no need for the intersection relation 'page_types' shown above - you only need those to normalise an m:n relationship.
Finally, each page has one or more contents.

What might be missing is something defining what the type of each bit of contents is, if page:contents is 1:n.

Having an ERD that contains a loop isn't necessarily an error, but it does mean there's a constraint that cannot be expressed in the diagram that is part of the model.

jack.mitchell · 8 Nov 2011 at 19:27

Ok, I've exported my tables as a PDF from phpMyAdmin for those of you who are interested.

http://devel.embed.me.uk/tables.pdf

cats_five · 9 Nov 2011 at 08:18

I take it this a schema you are designing yourself? If it is inspecting the schemas for existing free wiki software might help.