Get "non-existing" values from database

Question

I am having trouble with my database. I have three corresponding tables, where I in the first is holding some pages and in the second one holding some fields. The third one is a table holding content to all fields in the pages.

Tables:

• pages(PageID, Name)

• fields(FieldID, FieldName);

• pagefields(PageFieldID, FieldID, PageID)

• pagefieldcontents(PageFieldContentID, PageFieldID, FieldID, PageID, Content)

What I need is to make sure, that EVEN if there in the table "pagefields" is NO value to a field, but if the field is related to the page (in the pagefields table), it will get returned as a row with value as an empty string.

What I am doing now is the following:

SELECT
    pfc.ContentID as PFC_ContentID,
    pfc.Content as PFC_FieldContent,
    pfc.FieldID as PFC_FieldID
FROM
    pagesfieldcontents pfc
INNER JOIN
    pagefields pf
    ON pf.PageID = pfc.PageID
INNER JOIN
    fields ptf
    ON pf.FieldID = ptf.FieldID
INNER JOIN
    pages p
    ON p.PageID = pf.PageID
WHERE
    (some where-statement)
ORDER BY
    somefield desc

Hope you can understand my question - have a nice day.

Answer 1

INNER JOINs require there to be a row in the joined table for the first tabke's row to be returned. But you may have fields that font have content and you shill want those returned...

Change all your INNER joins to LEFT joins:

SELECT
  pfc.ContentID as PFC_ContentID,
  pfc.Content as PFC_FieldContent,
  pfc.FieldID as PFC_FieldID
FROM pages p
LEFT JOIN pagefields pf
  ON p.PageID = pf.PageID
LEFT JOIN fields ptf
  ON pf.FieldID = ptf.FieldID
LEFT JOIN pagesfieldcontents pfc
  ON p.PageID = pfc.PageID
  AND pf.FieldID = pfc.FieldID
WHERE some where-statement
ORDER BY somefield desc

I've also rearranged the table order and join conditions to what I guess you need.